Ustawianie separatorów i formatów danych podczas ładowania

Kiedy dane pochodzą z różnych systemów, ich format może się różnić. Jeden plik może używać przecinków jako separatorów, inny — średników, a w trzecim tabulator może być głównym sposobem oddzielania kolumn. Zła konfiguracja separatorów podczas ładowania danych może prowadzić do błędów lub złej interpretacji danych.

Mało tego, w prawdziwym życiu możesz trafić na sytuacje, gdzie trzeba załadować dane w formacie tekstowym bez standardowych nagłówków CSV, a także obsłużyć puste wartości i przewidzieć przypadki, gdy puste linie mają być interpretowane jako NULL. Dlatego ustawianie separatorów i formatów to nieodłączna część pracy z masowym ładowaniem danych.

Podstawowe separatory: jak z nimi działać?

W PostgreSQL komenda COPY daje elastyczność w ustawianiu separatorów. Zobaczmy, jak to działa.

Ustawianie separatorów

Domyślnie komenda COPY oczekuje, że kolumny w pliku CSV będą oddzielone przecinkami. Ale to nie zawsze jest wygodne lub możliwe: ktoś może używać średników (;), pionowych kresek (|) albo nawet tabulatora (\t).

Tak możesz wskazać separator za pomocą parametru DELIMITER:

COPY students FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER;

Jeśli zamiast przecinków są średniki:

COPY students FROM '/path/to/students.csv'
DELIMITER ';'
CSV HEADER;

Możesz nawet załadować plik z tabulatorem:

COPY students FROM '/path/to/students.tsv'
DELIMITER E'\t' 
CSV HEADER;

Tutaj E'\t' oznacza, że separatorem będzie znak tabulatora.

Ładowanie plików z niestandardowym separatorem

Praktyczny przykład: masz plik z danymi o kursach, gdzie używane są pionowe kreski (|) jako separatory. Dane w pliku wyglądają tak:

course_id|course_name|credits
1|SQL Basics|3
2|Advanced SQL|4
3|PostgreSQL Masterclass|5

Tak możesz załadować te dane do tabeli courses:

COPY courses(course_id, course_name, credits)
FROM '/path/to/courses.csv' 
DELIMITER '|' 
CSV HEADER;

Tutaj jasno mówimy PostgreSQL, że separatorem jest pionowa kreska.

Ustawianie formatów danych podczas ładowania

Separatory to tylko część zadania. Format danych w pliku też ma duże znaczenie. Zobaczmy podstawowe sposoby ustawiania formatów danych.

Ignorowanie pustych linii i ustawianie NULL

Często w dużych zbiorach danych trafiają się puste linie albo kolumny bez danych. PostgreSQL traktuje je jako puste stringi, jeśli nie powiesz inaczej. Żeby interpretować takie wartości jako NULL, możesz użyć parametru NULL AS:

Przykład. Załóżmy, że w twoim pliku są rekordy z pustymi wartościami:

id,first_name,last_name,email
1,John,Doe,
2,Jane,Smith,jane.smith@example.com
3,,Brown,james.brown@example.com

Ładowanie danych z interpretacją pustych wartości w kolumnie email jako NULL:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '';

W efekcie puste wartości w pliku będą zapisane jako NULL w tabeli.

Ignorowanie pustych linii

Czasem plik może mieć puste linie, których nie chcesz ładować. PostgreSQL pozwala automatycznie je ignorować.

Przykład. Plik z pustą linią:

id,first_name,last_name,email
1,John,Doe,john.doe@example.com

2,Jane,Smith,jane.smith@example.com

Użyj parametru IGNORE_BLANK_LINES:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '' 
IGNORE_BLANK_LINES;

Teraz puste linie będą ignorowane podczas ładowania.

Praca z niestandardowym formatem danych

Czasem możesz potrzebować załadować dane zapisane w formacie tekstowym, zamiast CSV. Na przykład linie w pliku są oddzielone pionowymi kreskami |, a dane nie mają nagłówka.

Przykład pliku:

1|John|Doe|john.doe@example.com
2|Jane|Smith|jane.smith@example.com

W takim przypadku możesz użyć takiego zapytania:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.txt' 
DELIMITER '|' 
NULL AS '' 
CSV;

Jeśli w pliku nie ma nagłówków, po prostu pomiń parametr HEADER.

Praktyczny przykład ustawiania formatów danych

Scenariusz: masz plik grades.tsv z ocenami studentów. Dane wyglądają tak:

student_id  course_id   grade
1   101     85
1   102     90
2   101     78
2   102     88
3   101     95
3   102 

Potrzebujesz:

1. Załadować plik z poprawną interpretacją pustych wartości jako NULL.
2. Upewnić się, że dane są załadowane poprawnie.

Rozwiązanie:

1. Stwórz tabelę grades:

CREATE TABLE grades (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade INTEGER
);

2. Załaduj dane z pliku:

COPY grades(student_id, course_id, grade)
FROM '/path/to/grades.tsv' 
DELIMITER E'\t' 
NULL AS '' 
CSV HEADER;

3. Sprawdź załadowane dane:

SELECT * FROM grades;

Oczekiwany wynik:

Rekomendacje i typowe błędy

Błąd: niezgodność separatora. Jeśli nie wskażesz poprawnego separatora, PostgreSQL wyrzuci błąd albo załaduje dane źle. Na przykład, jeśli w pliku jest średnik, ale zapomnisz o DELIMITER ';', komenda COPY potraktuje całą linię jako jedną kolumnę.

Błąd: zła interpretacja NULL. Jeśli nie ustawisz parametru NULL AS '', puste wartości w pliku mogą być traktowane jako puste stringi, co często powoduje błędy w obliczeniach albo filtrowaniu.

Błąd: zły format danych. Zła konfiguracja separatora albo błędy w pliku (np. użycie tabulatora zamiast spacji) mogą prowadzić do błędu typu: ERROR: null value in column violates not-null constraint.