Ik ben er vrij zeker van dat iedereen van ons wel eens in een situatie heeft gezeten waarin je een rapport moest genereren en/of gegevens uit een database moest halen en deze in een spreadsheet moest presenteren. In veel gevallen geven onze klanten de voorkeur aan Excel om spreadsheets/rapporten af te handelen, omdat, duh, het Excel is.
Dus, hoe pak je dit probleem aan? Kopieert en plakt u gegevens? Of gebruikt u een RDBMS GUI om het rapport in een spreadsheet te genereren? Vandaag laat ik u een kleine maar handige functie van PostgreSQL zien -COPY.
Hallo COPY#
Van de COPY-documentatie: “COPY verplaatst gegevens tussen PostgreSQL tabellen en standaard bestandssysteem bestanden. KOPIE NAAR kopieert de inhoud van een tabel naar een bestand, terwijl KOPIE VAN gegevens uit een bestand naar een tabel kopieert (waarbij de gegevens worden toegevoegd aan wat er al in de tabel staat). COPY TO kan ook de resultaten van een SELECT query kopiëren.”
Dus, wat doet COPY:
- Het kan de inhoud van een bestand (gegevens) naar een tabel kopiëren, of
- Het kan de inhoud van een tabel (of een SELECT query resultaat) naar een bestand kopiëren.
Als een lijst met kolommen is opgegeven, kopieert COPY alleen de gegevens in de gespecificeerde kolommen naar of uit het bestand. Als er kolommen in de tabel zijn die niet in de kolommenlijst staan, voegt COPY FROM de standaardwaarden voor die kolommen in.
COPY met een bestandsnaam instrueert de PostgreSQL-server om direct naar een bestand te lezen of ernaar te schrijven. Het bestand moet toegankelijk zijn voor de server en de naam moet worden opgegeven vanuit het oogpunt van de server. Wanneer STDIN of STDOUT wordt gespecificeerd, worden gegevens verzonden via de verbinding tussen de client en de server.
Klinkt goed? Laten we het eens proberen!
Disclaimer: COPY heeft de mogelijkheid om gegevens te lezen/schrijven van/naar CSV en Binary files. Hoewel er vast veel toepassingen zijn voor het gebruik van binaire bestanden, richt ik me in deze blogpost alleen op CSV-bestanden, omdat die voor mij het handigst zijn voor het overdragen van datasets.
COPY TO#
Wanneer u een CSV-bestand wilt maken van een SELECT query, of de volledige inhoud van een tabel in een CSV-bestand wilt dumpen, kunt u het commando “COPY … TO …” gebruiken.
Een SELECT-query gebruiken#
Wanneer u een resultatenverzameling naar een CSV-bestand wilt kopiëren, is de opmaak van het COPY-commando:
|
|
Of, een meer realistisch voorbeeld:
|
|
Zoals u ziet, gebruiken we het commando KOPIEER dat de resultaten kopieert naar een CSV-bestand op het lokale bestandssysteem. Je kunt met de query nog veel verder gaan. Hier is een voorbeeld van een project waar ik momenteel aan werk:
|
|
Zoals je ziet kun je elke SELECT query gebruiken die een data resultaat kan opleveren.Maar, wat is een CSV zonder headers, toch?
|
|
Als u aan het einde het trefwoord HEADER toevoegt, worden de headers in het CSV-bestand opgenomen, dat zijn de kolomnamen van de tabel.
Een ander belangrijk kenmerk van CSV-bestanden zijn de scheidingstekens. Afhankelijk van welk scheidingsteken u in het CSV-bestand wilt hebben, kunt u het teken in het commando opgeven:
|
|
Gebruik van een tabelnaam#
Wanneer u een hele tabel in een CSV wilt dumpen, is het commando echt eenvoudiger. U hoeft alleen de tabelnaam en het doelbestand op te geven:
|
|
Dat is alles.
COPY FROM#
Nu, wanneer u de gegevens van het CSV-bestand in een tabel wilt injecteren, kunt u het commando “COPY … FROM …” gebruiken. De syntaxis is zeer gelijkaardig, met slechts één sleutelverschil:
|
|
Of, met een voorbeeld uit het echte leven:
|
|
Outro#
COPY is een echt nette en coole functie van Postgres. Voor het brein heb ik geprobeerd deze blogpost kort en eenvoudig te houden. Als je gedachten of vragen hebt, stuur me dan gerust een commentaar. Of, als je geen zin hebt in een praatje, ga dan naar de COPY documentatie.
Vond je dit artikel leuk? Abonneer je op mijn nieuwsbrief en ontvang mijn verse berichten in je inbox. Het is kort en krachtig, en gaat maandelijks naar meer dan 1.000 abonnees.