Wie exportiere ich Daten aus PostgreSQL in eine CSV-Datei?

Müssen Sie schnell Daten an einen Kunden senden oder einen Bericht zur weiteren Analyse freigeben? Eine CSV-Datei ist eine großartige Option zum Austausch! Schauen wir uns an, wie Sie dieses Format verwenden können, um Daten aus einer PostgreSQL-Datenbank zu exportieren.

In diesem Artikel werden wir uns zunächst ansehen, was das CSV-Dateiformat ist und warum es beim Export Ihrer PostgreSQL-Datenbank nützlich ist. Dann werden wir einige Beispieldaten aus einer echten Datenbank exportieren, indem wir psql auf der Kommandozeile und pgAdmin, eine freie und Open-Source-Schnittstelle für PostgreSQL-Datenbanken, verwenden.

Bevor wir beginnen, sollten Sie sicherstellen, dass Sie Ihre PostgreSQL-Datenbank eingerichtet haben. Wenn Sie noch nicht mit PostgreSQL gearbeitet haben, schauen Sie sich unseren SQL für Anfänger in PostgreSQL Kurs an. Er enthält 130 interaktive Programmieraufgaben, die Ihnen den Einstieg in PostgreSQL erleichtern. Wenn Sie aber nur eine kurze Auffrischung brauchen, können Sie hier Ihre PostgreSQL-Kenntnisse üben.

Was sind CSV-Dateien?

CSV ist die Abkürzung für Comma-Separated Values. Mit diesem Dateiformat können Sie Daten im Klartext speichern, was sich hervorragend für die gemeinsame Nutzung von Daten in verschiedenen Anwendungen eignet.

Eine CSV-Datei enthält Datenzeilen, die einzelnen Datensätzen entsprechen; die erste Zeile besteht in der Regel aus den Spaltennamen. Die Werte in jeder Zeile werden durch ein Komma getrennt. Obwohl das Komma das am häufigsten verwendete Trennzeichen ist (wie der Name des Formats vermuten lässt), können auch andere Zeichen (z. B. Semikolon, Tabulator oder Leerzeichen) die Werte trennen.

Hier ist ein Beispiel für eine CSV-Datei. In den nächsten Abschnitten werden wir eine solche Datei exportieren:

store_id,revenue,day
1,100.42,2023-05-01
1,148.89,2023-05-02
2,238.98,2023-05-03

Wie Sie sehen können, enthält die erste Spalte die ID des Geschäfts, die zweite Spalte den Umsatz und die dritte Spalte das Datum. Jede Zeile ist ein Datensatz. Die Organisation ist ähnlich wie bei Tabellenkalkulationen und Datenbanken, aber alles ist in Textform!

Warum eine Datenbank als CSV-Datei exportieren?

CSV-Dateien können von fast jeder datenorientierten Software geöffnet werden. Wenn Sie Ihre PostgreSQL-Daten in eine CSV-Datei exportieren, können Sie Informationen leicht mit Kollegen austauschen, auch wenn diese andere Tools verwenden.

Das CSV-Format ist auch in vielen Datenanalysetools und Tabellenkalkulationen enthalten. Durch den Export in diesem Format können Sie Daten schnell und einfach für detaillierte Analysen, Visualisierungen und Berichte übertragen.

Exportieren von Daten aus PostgreSQL nach CSV

Die Daten

Dies wird unsere Beispieltabelle mit dem Namen sales. Jede Zeile gibt an, wie viel jedes Geschäft an einem bestimmten Tag verdient hat.

sales

Daten mit SQL-Abfragen exportieren

Wir werden zwei verschiedene SQL-Abfragen verwenden, um verschiedene Exportmethoden zu testen. (Es lohnt sich, unser PostgreSQL Cheat Sheet in der Nähe zu haben, um die Produktivität zu steigern, wenn Sie Ihre eigenen Abfragen schreiben). Dies ist die erste Abfrage:

SELECT * FROM sales

Diese erste Abfrage ist eine einfache Abfrage, die alles in der Tabelle auswählt. Sie soll sicherstellen, dass während des Exports keine Daten verloren gehen.

Hier ist die zweite (viel komplexere) Abfrage:

WITH max_revs (store_id, day, revenue, max_rev) AS (
	SELECT
		store_id,
		day,
		revenue, 
		MAX(revenue) OVER(PARTITION BY day) 
	FROM sales 
)
SELECT
	store_id,
	day,
	max_rev
FROM max_revs
WHERE revenue = max_rev;

Die zweite Abfrage simuliert einen einfachen Bericht; sie gibt für jeden Tag das umsatzstärkste Geschäft und den höchsten Umsatz selbst aus. So etwas könnte man bei der Arbeit in der Datenanalyse schreiben. Wir verwenden einen gemeinsamen Tabellenausdruck, um zunächst den höchsten Umsatz für den Tag auszuwählen; die äußere (zweite) Abfrage wählt die Filiale(n) aus, die mit dem von der CTE zurückgegebenen Umsatz übereinstimmen.

Wenn Sie mit CTEs und Fensterfunktionen noch nicht vertraut sind, lesen Sie den SQL Reporting Track und unseren Kurs über Fensterfunktionen in SQL. Doch nun zurück zum Exportieren von Daten aus PostgreSQL in eine CSV-Datei. Sobald wir die gewünschten Daten haben, müssen wir den Exportprozess starten.

Exportieren der Datenbank über die Befehlszeile

Zunächst wollen wir den Export einer PostgreSQL-Datenbank über die Befehlszeile untersuchen. Sie haben hier zwei Möglichkeiten: den Befehl \copy oder die Anweisung COPY. Wir werden die Unterschiede zwischen den beiden Methoden gleich erläutern.

Beide Methoden sind zwar weniger einfach als die Verwendung von pgAdmin, aber Sie haben wahrscheinlich schon alles, was Sie dafür brauchen. Also, fangen wir an.

Verbinden mit der Datenbank mit psql

Für beide Befehlszeilenoptionen werden wir psql verwenden. Dies ist ein Befehlszeilentool für PostgreSQL-Datenbanken.

Um sich mit der Datenbank zu verbinden, geben Sie den folgenden Befehl ein:

psql -h <hostname> -p <port> -d <database name> -U <username>

Ersetzen Sie hostname durch den Hostnamen (oder die Adresse) der Datenbank. Der Port wird auf dem Remote-Server zusammen mit dem Datenbanknamen und dem Benutzernamen angegeben. Danach werden Sie zur Eingabe eines Passworts aufgefordert.

Sobald Sie mit der Datenbank verbunden sind, können Sie entweder den Befehl \copy oder die Methode COPY verwenden, um Ihre Daten zu exportieren. Wir beginnen mit der Demonstration von \copy.

1. exportieren von Daten mit \copy

Der Befehl \copy kopiert Ihr lokales Abfrageergebnis direkt in eine lokale Datei auf Ihrem Rechner. Er funktioniert auf der Client-Seite, ist also eine gute Wahl, wenn Sie nur über Leseberechtigungen für die Datenbank verfügen.

Hier ist die Syntax:

\copy (query) to ‘filename’ with cvs [header]

Sie können das optionale Argument header einfügen, wenn Sie möchten, dass die generierte Tabelle eine zusätzliche Zeile am Anfang enthält, die alle Spaltennamen enthält. Um die Klarheit der Ergebnisse zu gewährleisten, werde ich dieses Argument in allen zukünftigen Befehlen verwenden.

Schauen wir uns ein paar Beispiele an.

Beispiel 1: Kopieren der gesamten Tabelle

Code:

\copy (SELECT * FROM sales) to ‘/dbExport/sales_full_table.csv’ with csv header

Erläuterung: Nach dem Ausführen der Abfrage SELECT * FROM sales kopiert Ihr Computer die Ergebnisse in die angegebene CSV-Datei. Es werden keine weiteren Anfragen an die Datenbank gesendet.

Beispiel 2: Abfrageergebnisse kopieren

Code:

\copy (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) to ‘/dbExport/sales_report_query.csv’ with csv header

Erläuterung: Hier wird das gleiche Prinzip angewandt. Die Datenbank führt diese Berichtsabfrage aus und Ihr lokaler Computer kopiert die Ergebnisse in eine Datei.

Der Befehl \copy ist einfach zu verwenden - und in den meisten Fällen genauso schnell wie eine durchschnittliche Abfrage. Das liegt daran, dass die resultierende Datei lokal erzeugt wird, anstatt von der Datenbank gesendet zu werden.

2. die COPY-Anweisung verwenden

Anders als der Befehl \copy wird die Anweisung COPY auf der Datenbankseite ausgeführt. Wir nennen diese Anweisung eine Anweisung, weil COPY tatsächlich Teil eines Befehls ist, der von der Datenbank ausgeführt wird. Das bedeutet, dass die resultierende Datei auf dem entfernten Server gespeichert wird, was Sie bei der Wahl des Dateipfads berücksichtigen sollten. Das Speichern auf einem entfernten Server bedeutet auch, dass Sie über PostgreSQL-Superuser-Rechte (root) verfügen sollten.

Die Syntax für die COPY-Anweisung sieht wie folgt aus:

COPY { table | (query) } 'filename' [ DELIMITER 'delimiter' ] [HEADER]

Der Einfachheit halber habe ich einige Argumente weggelassen; wenn Sie die vollständige Syntax sehen wollen, besuchen Sie die PostgreSQL-Dokumentation

Beispiel 1: Kopieren der gesamten Tabelle

Code:

COPY sales TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Erläuterung: Wie zu erwarten, wird hier die gesamte Verkaufstabelle in die Datei sales_full_table.csv kopiert. Beachten Sie, dass wir das Trennzeichen auf Komma gesetzt haben und die Kopfzeile eingeschlossen ist.

Beispiel 2: Kopieren der Abfrageergebnisse

Code:

COPY (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Erläuterung: Wie der Untertitel schon sagt, werden die Ergebnisse der Abfrage in die Datei sales_full_table.csv exportiert. Hier ist ein Beispiel für die Daten in Tabellenform:

Wie Sie sehen können, ist das Ergebnis das gleiche wie beim Befehl \copy; der einzige Unterschied ist der Ort, an dem die resultierende Datei gespeichert wird.

Daten exportieren mit pgAdmin

Werfen wir nun einen Blick auf die zweite Option, die eine grafische Benutzeroberfläche (GUI) verwendet. Vielleicht sind Sie bereits mit pgAdmin vertraut; es ist ein kostenloses und quelloffenes PostgreSQL-Datenbankverwaltungsprogramm. Dies ist ein einfacher und unkomplizierter Weg, um Daten aus einer Postgres-Datenbank zu exportieren.

Verbinden Sie sich mit der Datenbank

Öffnen Sie pgAdmin und klicken Sie auf Add New Server.

Auf der Seite Allgemein wählen Sie einen Namen für die Verbindung.

Auf der Seite Verbindung geben Sie den Hostnamen ("localhost", wenn Sie die Datenbank auf Ihrem eigenen Computer betreiben), den Namen der Datenbank, den Benutzernamen und das Passwort ein.

Klicken Sie abschließend auf Speichern. Wenn die Verbindung korrekt hergestellt wurde, zeigt Ihre Dashboard-Seite nun Diagramme zur Aktivität Ihrer Datenbank an.

Abfrageergebnisse exportieren

Öffnen Sie zunächst das Abfragetool, indem Sie entweder auf das Datenbanksymbol (drei Scheiben) in der oberen Symbolleiste klicken oder Alt+Shift+Q verwenden. Sie können nun die Datenbank abfragen, indem Sie Abfragen in das Textfeld schreiben und sie entweder über die Schaltfläche "Abspielen" am oberen Rand oder die Taste F5 ausführen.

Sobald Sie eine Abfrage ausgeführt haben, wird das Ergebnis im unteren Fenster angezeigt. Um das Ergebnis in einer CSV-Datei zu speichern, klicken Sie auf die Schaltfläche "Download" direkt über den Ergebnisdaten.

Wie Sie sehen können, sind die Ergebnisse die gleichen wie bei den Kommandozeilen-Tools. Der Hauptunterschied besteht darin, dass pgAdmin den Wechsel zwischen der Erstellung von Abfragen und dem Speichern der Ergebnisse wesentlich einfacher macht.

Erfahren Sie mehr über den Export von Daten mit CSV-Dateien

Das Exportieren von Datenbanken in CSV-Dateien ist ein Vergnügen! Jetzt, wo Sie wissen, wie Sie alle benötigten Daten exportieren können, haben Sie vielleicht eine weitere Frage: Wie kann ich Daten in eine Postgres-Datenbank importieren? Lesen Sie diesen kurzen Artikel über den Import von Daten in PostgreSQL mit pgAdmin, um es herauszufinden.

Und wenn Sie Ihr Wissen über PostgreSQL erweitern möchten, schauen Sie sich unseren vollständigen Kurs A bis Z mit PostgreSQL an, der über 1.000 interaktive Übungen enthält. Viel Spaß beim Lernen!