Import danych z plików CSV za pomocą komendy `COPY`

Cześć, ekipo! Wyobraź sobie, że masz tabelkę w Excelu z 10 000 wierszy, a szef mówi: "Wrzuć to do bazy!" Hmm... jeśli spróbujesz wklepywać dane ręcznie, to... raczej przydadzą Ci się nie tylko umiejętności SQL, ale i urlop. 😅

Na szczęście w PostgreSQL jest komenda, która działa jak teleport z CSV do bazy — to COPY. Ładuje dane błyskawicznie, hurtowo i bez zbędnego zamieszania. Dzisiaj ogarniemy, jak działa COPY, jak przygotować dane i co zrobić, jeśli w pliku pojawi się "pływający przecinek" albo jakiś dziwny znak. Lecimy!

Podstawy komendy COPY

COPY — to komenda PostgreSQL, która pozwala przenosić dane między tabelą a systemem plików. Jest przydatna do masowego ładowania danych do bazy albo ich eksportu.

Przykład składni COPY do ładowania danych do tabeli:

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

Główne parametry:

• FROM 'path/to/your/file.csv': określa ścieżkę do Twojego pliku CSV.
• DELIMITER ',': wskazuje znak separatora (tutaj przecinek).
• CSV HEADER: oznacza, że plik CSV ma nagłówek (pierwszy wiersz z nazwami kolumn).

Komenda COPY działa bezpośrednio na serwerze, co sprawia, że jest mega szybka przy dużych ilościach danych.

Różnica między COPY a \COPY

Czasem możesz się spotkać z lekkim zamieszaniem między COPY a \COPY.

• COPY: działa po stronie serwera. Używana do ładowania plików, które są na serwerze.
• \COPY: komenda kliencka dostępna w psql. Ładuje dane z Twojego lokalnego kompa.

Jeśli pracujesz na swoim komputerze, pewnie będziesz używać \COPY. O niej pogadamy za chwilę.

Przykład użycia COPY

Przejdźmy od razu do przykładu, żeby lepiej ogarnąć, jak to działa w praktyce.

Krok 1: Przygotowanie tabeli

Wyobraź sobie, że tworzymy tabelę do przechowywania info o studentach:

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

Krok 2: Przygotowanie pliku CSV

Format Twojego pliku CSV musi pasować do tabeli. Przykład pliku 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

Pamiętaj: kolejność kolumn w CSV musi odpowiadać kolumnom w tabeli, a typy danych muszą się zgadzać (np. data w formacie YYYY-MM-DD).

Krok 3: Ładowanie danych

Aby załadować plik students.csv do tabeli students, użyj tej komendy:

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

Co tu się dzieje?

• Podajesz nazwę tabeli students i kolumny, do których ładujesz dane.
• Ścieżka do pliku wskazuje jego lokalizację.
• Separator , mówi, że dane są oddzielone przecinkami.
• Parametr CSV HEADER informuje PostgreSQL, że pierwszy wiersz pliku to nazwy kolumn.

Efekt:

Po wykonaniu komendy dane z pliku lądują w tabeli i możesz je od razu sprawdzić:

SELECT * FROM students;

Ograniczenia i specyfika

Żeby uniknąć błędów, upewnij się, że Twój plik CSV spełnia te wymagania:

1. Brak ukrytych znaków lub spacji w wierszu nagłówka.
2. Użycie poprawnego kodowania: w PostgreSQL zwykle polecane jest UTF-8.
3. Zgodność separatora z parametrem DELIMITER.

Typowe błędy przy ładowaniu COPY:

Niezgodność struktury danych. Na przykład, jeśli próbujesz załadować tekst do pola numerycznego:

ERROR: invalid input syntax for type numeric: "abc"

Żeby tego uniknąć, sprawdź, czy dane pasują do oczekiwanego typu.

Błąd ścieżki do pliku.

Jeśli plik jest poza dostępnym katalogiem PostgreSQL na serwerze, dostaniesz błąd:

ERROR: could not open file "/path/to/your/file.csv" for reading: Permission denied

Rozwiązaniem jest poprawne ustawienie uprawnień do pliku.

Jak sprawdzić, czy dane się poprawnie załadowały?

Po załadowaniu danych warto upewnić się, że wszystko poszło OK. Na przykład:

Sprawdzenie liczby wierszy:

SELECT COUNT(*) FROM students;

Wyszukiwanie pustych wartości (np. jeśli pole grade ma NULL):

SELECT * FROM students WHERE grade IS NULL;

Przydatne porady do pracy z COPY

Loguj błędy. Jeśli chcesz zapisywać błędy do osobnego pliku, użyj parametru LOG ERRORS. (W PostgreSQL wersja 12+).

Wyłącz indeksy i triggery.

Przy ładowaniu dużych ilości danych możesz tymczasowo wyłączyć indeksy:

ALTER TABLE students DISABLE TRIGGER ALL;

Używaj transakcji.

Pozwala to załadować dane "atomowo" — albo wszystko się załaduje, albo w razie błędu wszystko się cofnie:

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

Zastosowanie w praktyce

Umiejętność korzystania z komendy COPY przyda się nie tylko adminom baz danych — programistom też może mocno ułatwić życie. Wyobraź sobie, że dostajesz dużą paczkę danych z zewnętrznego API: najpierw zapisujesz je do pliku CSV, a potem błyskawicznie wrzucasz do PostgreSQL — i możesz zaczynać analizę. Albo, powiedzmy, koledzy proszą o przeniesienie ich starej bazy do nowego systemu na PostgreSQL. W takich sytuacjach COPY to prawdziwy lifehack.

Pliki CSV pojawiają się w realnych projektach non stop. Na przykład:

• Ładujesz statystyki sprzedaży do systemu analitycznego.
• Importujesz listę użytkowników z zewnętrznego CRM.
• Przenosisz dane z Excela do PostgreSQL bez zbędnych kombinacji.

Na tym kończymy nasze pierwsze spotkanie z komendą COPY. W następnej lekcji pogadamy o tym, jak przygotować tabele do ładowania danych i przemyśleć ich strukturę tak, żeby wszystko działało szybko i niezawodnie. A póki co nie bój się eksperymentować — COPY daje naprawdę wygodne możliwości do pracy z dużą ilością informacji.