Użycie COPY do ładowania danych z serwera

Kiedy twoje dane już są na serwerze, możesz użyć polecenia COPY, żeby załadować je do tabel PostgreSQL. To szczególnie przydatne, jeśli pracujesz z dużymi ilościami danych albo twój serwer PostgreSQL stoi na osobnej maszynie.

Polecenie COPY wykonywane po stronie serwera jest mega wygodne w prawdziwych projektach. Po pierwsze, działa dużo szybciej, bo pliki już są na serwerze i nie trzeba ich przesyłać przez sieć. To też sprawia, że proces jest bezpieczniejszy: nie musisz kopiować danych z lokalnego kompa, więc ryzyko wycieku jest mniejsze. Dodatkowo ładowanie można łatwo wrzucić do automatycznych skryptów serwerowych albo usług działających w tle — na przykład przy regularnym odświeżaniu tabel analitycznych.

Składnia polecenia COPY

Polecenie COPY jest proste, ale jest kilka kluczowych rzeczy, na które trzeba zwrócić uwagę:

COPY table_name
FROM '/path/to/file.csv'
WITH (FORMAT CSV, HEADER TRUE);

• table_name — nazwa tabeli, do której chcesz załadować dane.
• /path/to/file.csv — pełna ścieżka do pliku na serwerze.
• Opcje WITH pozwalają określić format pliku, obecność nagłówków, separatory i wiele innych rzeczy.

Przykład użycia polecenia COPY

Przeanalizujmy mały przykład. Załóżmy, że musisz załadować plik CSV z danymi o studentach do bazy PostgreSQL. Plik jest na serwerze pod ścieżką /var/lib/postgresql/data/students.csv.

Krok 1. Tabela na dane

Najpierw upewnij się, że w bazie jest tabela, do której można ładować dane:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    registration_date DATE
);

Ta tabela będzie przechowywać dane o studentach: ich imię, email i datę rejestracji.

Krok 2. Plik CSV

Tak może wyglądać plik /var/lib/postgresql/data/students.csv:

id,name,email,registration_date
1,Alex Lin,alex.lin@example.com,2023-09-01
2,Maria Chi,maria.chi@example.com,2023-09-02
3,Peter Ming,peter.ming@example.com,2023-09-02

Krok 3. Polecenie COPY

Teraz możemy użyć COPY, żeby załadować dane z pliku do tabeli:

COPY students
FROM '/var/lib/postgresql/data/students.csv'
WITH (FORMAT CSV, HEADER TRUE);

Tu:

• FORMAT CSV mówi PostgreSQL, że plik jest w formacie CSV.
• HEADER TRUE oznacza, że pierwszy wiersz pliku zawiera nagłówki kolumn.

Po wykonaniu polecenia dane z pliku trafią do tabeli students.

Sprawdzenie wyniku

Po załadowaniu danych upewnij się, że wszystko poszło OK:

SELECT * FROM students;

Zobaczysz wiersze z pliku załadowane do twojej tabeli:

Ustawianie uprawnień dostępu

PostgreSQL musi mieć dostęp do pliku, żeby użyć polecenia COPY. Jeśli uprawnienia są źle ustawione, pojawią się błędy. Na przykład:

ERROR:  could not open file "/var/lib/postgresql/data/students.csv" for reading: Permission denied

Żeby uniknąć takich problemów, upewnij się, że:

1. Plik jest dostępny do odczytu przez użytkownika PostgreSQL. Zwykle to użytkownik postgres.
2. Sprawdź uprawnienia do pliku i katalogu, w którym się znajduje:

ls -l /var/lib/postgresql/data/students.csv

Jeśli uprawnienia są za słabe, możesz je zmienić:

chmod 644 /var/lib/postgresql/data/students.csv
chown postgres:postgres /var/lib/postgresql/data/students.csv

Tu mówimy o Linuxie. Ustawianie uprawnień w Windows zwykle robi admin Windows i to już poza naszym kursem.

Ograniczenia polecenia COPY

Praca z COPY ma kilka specyficznych rzeczy, o których warto pamiętać:

Ścieżka do pliku: podawaj ścieżkę absolutną, bo PostgreSQL może nie rozpoznać ścieżek względnych.

Kodowanie pliku: jeśli plik CSV ma inne kodowanie niż baza (np. Windows-1251 zamiast UTF-8), trzeba jawnie podać kodowanie:

COPY students
FROM '/var/lib/postgresql/data/students.csv'
WITH (FORMAT CSV, ENCODING 'WIN1251', HEADER TRUE);

Błędy struktury pliku: jeśli struktura pliku nie zgadza się ze strukturą tabeli albo dane są niepoprawne (np. tekst ładowany do pola liczbowego), ładowanie skończy się błędem. Sprawdź dane przed ładowaniem!

Praktyczne zastosowanie

Użycie COPY do ładowania danych z serwera ma mnóstwo zastosowań:

• Migracja danych: możesz przenosić duże ilości danych między serwerami albo bazami danych.
• Integracja z zewnętrznymi systemami: wiele systemów eksportuje dane do plików CSV. COPY pozwala szybko wrzucić takie dane do PostgreSQL.
• Przygotowanie raportów analitycznych: automatyczne ładowanie danych z serwera przyspiesza analizę dużych ilości informacji.

Typowe błędy i jak je rozwiązać

Pracując z COPY, możesz natknąć się na kilka błędów:

Problem: Plik niedostępny

Komunikat błędu: could not open file for reading: Permission denied.

Rozwiązanie: upewnij się, że plik jest dostępny dla użytkownika PostgreSQL (postgres) i sprawdź uprawnienia.

Problem: Zły format pliku

Komunikat błędu: malformed CSV line.

Rozwiązanie: sprawdź plik pod kątem pustych linii, błędów w danych albo dziwnych znaków. Upewnij się, że separator jest poprawny.

Problem: Niezgodność struktury danych

Komunikat błędu: ERROR: invalid input syntax for type integer.

Rozwiązanie: upewnij się, że kolumny w tabeli odpowiadają strukturze danych w pliku. Na przykład dane liczbowe muszą trafiać do kolumn liczbowych, a daty — do kolumn typu DATE.

Teraz masz już wszystkie potrzebne skille, żeby efektywnie używać COPY do ładowania danych z serwera. Wykorzystuj te umiejętności w swoich projektach, żeby oszczędzać czas i podnosić wydajność pracy z bazami danych.