Der PostgreSQL COPY-Befehl

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:

Weitere Anmerkungen:

• Der Parameter filename enthä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 \copy ein 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