Datenimport aus CSV-Dateien mit dem Befehl `COPY`

Hey Leute! Stell dir vor, du hast eine Excel-Tabelle mit 10.000 Zeilen und dein Chef sagt: "Lad das mal bitte in die Datenbank hoch!" Hm... Wenn du versuchst, die Daten per Hand einzufügen, brauchst du wahrscheinlich nicht nur SQL-Skills, sondern auch Urlaub. 😅

Zum Glück gibt’s in PostgreSQL einen Befehl, der wie ein Teleport von CSV in die Datenbank funktioniert – das ist COPY. Damit werden Daten sofort, in Massen und ohne großes Drama geladen. Heute schauen wir uns an, wie COPY funktioniert, wie du die Daten vorbereitest und was du tun kannst, wenn plötzlich ein "schwimmendes Komma" oder ein komisches Zeichen in der Datei auftaucht. Los geht’s!

Grundlagen des COPY-Befehls

COPY ist ein PostgreSQL-Befehl, mit dem du Daten zwischen einer Tabelle und dem Dateisystem verschieben kannst. Das ist super praktisch, um große Datenmengen in die Datenbank zu laden oder zu exportieren.

Beispiel-Syntax für COPY zum Laden von Daten in eine Tabelle:

COPY table_name FROM 'path/to/your/file.csv' DELIMITER ',' CSV HEADER;

Wichtige Parameter:

• FROM 'path/to/your/file.csv': gibt den Pfad zu deiner CSV-Datei an.
• DELIMITER ',': legt das Trennzeichen fest (hier ist es das Komma).
• CSV HEADER: sagt, dass die CSV-Datei eine Kopfzeile hat (erste Zeile mit Spaltennamen).

Der COPY-Befehl läuft direkt auf dem Server, was ihn mega schnell macht, wenn du mit großen Datenmengen arbeitest.

Unterschied zwischen COPY und \COPY

Manchmal gibt’s ein bisschen Verwirrung zwischen COPY und \COPY.

• COPY: läuft auf der Serverseite. Wird genutzt, um Dateien zu laden, die auf dem Server liegen.
• \COPY: ist ein Client-Befehl, den du in psql nutzen kannst. Damit lädst du Daten von deinem lokalen Rechner hoch.

Wenn du auf deinem eigenen Rechner arbeitest, wirst du meistens \COPY verwenden. Dazu später mehr.

Beispiel für die Nutzung von COPY

Lass uns direkt ein Beispiel anschauen, damit das Prinzip klar wird.

Schritt 1: Tabelle vorbereiten

Stell dir vor, wir legen eine Tabelle an, um Infos über Studenten zu speichern:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    grade FLOAT
);

Schritt 2: CSV-Datei vorbereiten

Das Format deiner CSV-Datei muss zur Tabelle passen. Beispiel für die Datei students.csv:

first_name,last_name,date_of_birth,grade
John,Doe,2001-05-15,85.5
Jane,Smith,2000-12-22,90.0
Alice,Johnson,2002-03-10,78.0
Bob,Brown,2001-08-30,NULL

Achte darauf: Die Reihenfolge der Spalten in der CSV muss zu den Spalten in der Tabelle passen und die Datentypen müssen stimmen (zum Beispiel muss das Datum im Format YYYY-MM-DD sein).

Schritt 3: Daten laden

Um die Datei students.csv in die Tabelle students zu laden, nutze diesen Befehl:

COPY students (first_name, last_name, date_of_birth, grade)
FROM '/path/to/your/students.csv'
DELIMITER ',' 
CSV HEADER;

Was passiert hier?

• Wir geben den Tabellennamen students und die Spalten an, in die die Daten geladen werden.
• Der Pfad zur Datei zeigt, wo sie liegt.
• Das Trennzeichen , sagt, dass die Daten durch Kommas getrennt sind.
• Mit CSV HEADER weiß PostgreSQL, dass die erste Zeile die Spaltennamen enthält.

Ergebnis:

Nach dem Ausführen des Befehls sind die Daten aus der Datei in der Tabelle und du kannst sie direkt checken:

SELECT * FROM students;

Einschränkungen und Besonderheiten

Damit keine Fehler passieren, achte darauf, dass deine CSV-Datei folgende Anforderungen erfüllt:

1. Keine versteckten Zeichen oder Leerzeichen in der Kopfzeile.
2. Die richtige Kodierung verwenden: In PostgreSQL wird meistens UTF-8 empfohlen.
3. Das Trennzeichen muss mit dem angegebenen DELIMITER-Parameter übereinstimmen.

Typische Fehler beim Laden mit COPY:

Struktur passt nicht zu den Daten. Zum Beispiel, wenn du versuchst, einen Textwert in ein Zahlenfeld zu laden:

ERROR: ungültige Eingabesyntax für Typ numeric: "abc"

Damit sowas nicht passiert, check vorher, ob die Daten zum erwarteten Typ passen.

Fehlerhafter Dateipfad.

Wenn die Datei außerhalb des für PostgreSQL zugänglichen Verzeichnisses auf dem Server liegt, bekommst du diesen Fehler:

ERROR: konnte Datei "/path/to/your/file.csv" nicht zum Lesen öffnen: Permission denied

Die Lösung ist, die Zugriffsrechte für die Datei richtig einzustellen.

Wie prüfst du, ob die Daten korrekt geladen wurden?

Nach dem Laden ist es sinnvoll zu checken, ob alles geklappt hat. Zum Beispiel:

Gesamtanzahl der Zeilen prüfen:

SELECT COUNT(*) FROM students;

Leere Werte finden (zum Beispiel, wenn das Feld grade NULL enthält):

SELECT * FROM students WHERE grade IS NULL;

Nützliche Tipps für die Arbeit mit COPY

Fehler loggen. Wenn du Fehler in eine separate Datei schreiben willst, nutze den Parameter LOG ERRORS. (Ab PostgreSQL Version 12+).

Indizes und Trigger deaktivieren.

Beim Laden großer Datenmengen kannst du Indizes vorübergehend ausschalten:

ALTER TABLE students DISABLE TRIGGER ALL;

Transaktionen nutzen.

Damit kannst du die Daten "atomar" laden – entweder alles wird geladen oder bei einem Fehler wird alles zurückgesetzt:

BEGIN;
COPY students FROM '/path/to/your/file.csv' CSV HEADER;
COMMIT;

Praktische Anwendung

Den COPY-Befehl zu kennen, hilft nicht nur Datenbank-Admins – auch für Entwickler macht das Leben viel leichter. Stell dir vor, du bekommst einen riesigen Datensatz von einer externen API: Erst speicherst du ihn als CSV-Datei und dann lädst du ihn blitzschnell in PostgreSQL – und schon kannst du mit der Analyse starten. Oder Kollegen bitten dich, ihre alte Datenbank in ein neues PostgreSQL-System zu übertragen. In solchen Fällen ist COPY echt ein Lebensretter.

CSV-Dateien begegnen dir in echten Projekten ständig. Zum Beispiel:

• Du lädst Verkaufsstatistiken in ein Analyse-System.
• Importierst eine Userliste aus einem externen CRM.
• Überträgst Daten aus Excel nach PostgreSQL – ganz ohne großes Theater.

Damit sind wir am Ende unseres Einstiegs in den COPY-Befehl. In der nächsten Vorlesung sprechen wir darüber, wie du Tabellen für den Datenimport vorbereitest und ihre Struktur so planst, dass alles schnell und zuverlässig läuft. Bis dahin: Keine Angst vorm Ausprobieren – COPY macht die Arbeit mit großen Datenmengen echt easy!