Der COPY-Befehl unter PostgreSQL erlaubt es, eine CSV-Datei direkt in eine Tabelle zu importieren bzw. Daten aus einer Tabelle direkt in eine CSV-Datei zu exportieren. Dieser Blog-Artikel erklärt, wie genau das funktioniert und was der Unterschied zwischen einem backend copy und einem frontend copy ist. Wir arbeiten dabei mit PostgreSQL 14 oder höher.
Einführung
Der PostgreSQL-Copy Befehl ist ein SQL-Befehl, der Daten zwischen einer Tabelle und einer Datei kopiert, und zwar in beide Richtungen.
Es werden die Dateiformate text, csv und binary unterstützt. Im weiteren Verlauf dieses Blog-Artikels konzentrieren wir uns auf das CSV-Format. CSV bedeutet Comma-Separated Values und ist im RFC 4180 beschrieben. CSV-Dateien lassen sich mit einem einfachen Texteditor oder mit einem Tabellenkalkulationsprogramm wie MS Excel bearbeiten.
Der Syntax für das Kopieren aus einer Eingabedatei in eine PostgreSQL-Tabelle lautet:
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
[ WHERE condition ]
Der Syntax für das Kopieren aus einer PostgreSQL-Tabelle in eine Ausgabedatei lautet:
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
Die wichtigsten Optionen für CSV sind:
| Option | Beschreibung |
|---|---|
FORMAT format_name |
Standard ist text. Wir benötigen den Wert csv. |
DELIMITER 'delimiter_character' |
Das Trennzeichen für das CSV-Format. Standard ist ,. |
NULL 'null_string' |
Der String, der den NULL-Wert repräsentiert. Standard ist ein Leerstring. |
HEADER [ boolean ] |
Angabe, ob Datei Kopfzeile enthält oder nicht. Gilt nur für text und csv. |
QUOTE 'quote_character' |
Das Anführungszeichen für das CSV-Format. Standard ist ". |
ESCAPE 'escape_character' |
Das Escape-Zeichen für das CSV-Format. Standard ist das definierte Anführungszeichen. |
ENCODING 'encoding_name' |
Die Zeichenkodierung der Datei |
Weitere Anmerkungen:
-
Der Parameter
filenameenthält einen lokalen Dateipfad aus Sicht des PostgreSQL-Servers (backend copy). Soll die Eingabe- oder Ausgabedatei auf einem anderen Rechner liegen, muss die Eingabe (STDIN) bzw. Ausgabe (STDOUT) umgeleitet werden. Dies macht sich psql (PostgreSQL interactive terminal) zu Nutze und erlaubt mittels\copyein frontend copy. -
Der
WHERE-Parameter erlaubt eine Filterung beim Import aus einer Eingabedatei. Zeilen, welche die Bedingung nicht erfüllen werden nicht nach PostgreSQL importiert. -
Beim Kopieren von Daten aus PostgreSQL in eine Ausgabedatei, kann neben der Angabe einer Tabelle auch eine SQL-Query-Anweisung (
query-Parameter) genutzt werden.
Beispiele
Die folgenden Beispiele lassen sich alle hervorragend mit Hilfe von pgAdmin ausführen. pgAdmin ist freie Open-Source-Software mit grafischer Oberfläche, um PostgreSQL-Datenbanken zu entwickeln und zu administrieren. Der folgende Blog-Artikel erklärt die Installation, Konfiguration und das Aktualisieren von pgAdmin 4 unter Windows 11.
Vorbereitung
Zunächst erzeugen wir eine Textdatei mit Namen test.csv und folgendem Inhalt:
Id;Name;Birthdate;Alive
1;John;08-12-1940;false
2;Paul;18-06-1942;true
3;George;25-02-1943;false
4;Ringo;07-07-1940;true
Dann erzeugen wir mit folgendem SQL-Befehl eine neue SQL-Tabelle in einer Datenbank (nennen wir sie testdb):
CREATE TABLE IF NOT EXISTS public."Test"
(
"Id" int,
"Name" text COLLATE pg_catalog."default",
"Birthdate" date,
"Alive" bool
);
Import aus einer CSV-Datei
Beispiel 1
Die Namen der Spalten in der CSV-Datei müssen den Namen der Spalten in der Datenbanktabelle entsprechen.
Backend Copy:
COPY public."Test"
FROM 'C:\Import\test.csv'
DELIMITER ';'
CSV HEADER
ENCODING 'UTF8';
Frontend Copy:
testdb=# \copy public."Test" FROM 'C:\Import\test.csv' DELIMITER ';' CSV HEADER ENCODING 'UTF8';
Beispiel 2
Die Namen der Spalten in der CSV-Datei müssen nicht den Namen der Spalten in der Datenbanktabelle entsprechen. Die Reihenfolge muss aber mit der im Befehl übereinstimmen.
Backend Copy:
COPY public."Test"("Id", "Name", "Birthdate", "Alive")
FROM 'C:\Import\test.csv'
DELIMITER ';'
CSV HEADER
ENCODING 'UTF8';
Frontend Copy:
testdb=# \copy public."Test"("Id", "Name", "Birthdate", "Alive") FROM 'C:\Import\test.csv' DELIMITER ';' CSV HEADER ENCODING 'UTF8';
Export in eine CSV-Datei
Beispiel 1
Export der kompletten Datenbanktabelle in die CSV-Datei.
Backend Copy:
COPY public."Test"
TO 'C:\Export\test.csv'
DELIMITER ';'
CSV HEADER
ENCODING 'UTF8';
Frontend Copy:
testdb=# \copy public."Test" TO 'C:\Export\test.csv' DELIMITER ';' CSV HEADER ENCODING 'UTF8';
Das Ergebnis sieht wie folgt aus:
Id;Name;Birthdate;Alive
1;John;1940-12-08;f
2;Paul;1942-06-18;t
3;George;1943-02-25;f
4;Ringo;1940-07-07;t
Beispiel 2
Nur die Spalten Name und Birthdate exportieren.
Backend Copy:
COPY public."Test"("Name", "Birthdate")
TO 'C:\Export\test.csv'
DELIMITER ';'
CSV HEADER
ENCODING 'UTF8';
Frontend Copy:
testdb=# \copy public."Test"("Name", "Birthdate") TO 'C:\Export\test.csv' DELIMITER ';' CSV HEADER ENCODING 'UTF8';
Das Ergebnis sieht wie folgt aus:
Id;Name;Birthdate;Alive
1;John;1940-12-08;f
2;Paul;1942-06-18;t
3;George;1943-02-25;f
4;Ringo;1940-07-07;t
Beispiel 3
Nur Zeilen mit der Bedingung Alive = true exportieren.
Backend Copy:
COPY (SELECT * FROM public."Test" WHERE "Alive" = true)
TO 'C:\Export\test.csv'
DELIMITER ';'
CSV HEADER
ENCODING 'UTF8';
Frontend Copy:
testdb=# \copy (SELECT * FROM public."Test" WHERE "Alive" = true) TO 'C:\Export\test.csv' DELIMITER ';' CSV HEADER ENCODING 'UTF8';
Das Ergebnis sieht wie folgt aus:
Id;Name;Birthdate;Alive
2;Paul;1942-06-18;t
4;Ringo;1940-07-07;t