Импорт и экспорт CSV в PostgreSQL

CSV — простой текстовый формат с разделителями (обычно запятые), удобный для импорта/экспорта в БД. Определение: CSV — строка записей, где каждая запись содержит поля, разделённые разделителем.

Создание CSV-файла

Ниже — пример CSV, который можно сохранить как /tmp/sample.csv. Первой строкой идёт заголовок (header). Кодировка файла должна соответствовать базе (часто UTF-8).

id,firstname,lastname,email
1,Riannon,Pulsifer,Riannon.Pulsifer@example.com
2,Moyna,Palocz,Moyna.Palocz@example.com
3,Roslyn,Bearnard,Roslyn.Bearnard@example.com
4,Gloria,Aldric,Gloria.Aldric@example.com
5,Felice,Greenwald,Felice.Greenwald@example.com

Важно: если в полях используются запятые, оборачивайте значения в кавычки. Для переносов строк внутри поля используйте экранирование или кавычки по стандарту CSV.

Создание таблицы в PostgreSQL

Перед импортом создайте таблицу с подходящими типами данных. Пример SQL:

CREATE TABLE employees(
  id int NOT NULL,
  firstname varchar(100),
  lastname varchar(100),
  email varchar(255)
);

Примечание: лучше использовать varchar вместо char, чтобы не хранить лишние пробелы.

Импорт CSV в PostgreSQL

Есть две логики: серверная (COPY … FROM) и клиентская (psql \copy). Выбирайте в зависимости от прав доступа и расположения файла.

Импорт на стороне сервера

Команда COPY читает файл на стороне сервера PostgreSQL — поэтому путь должен быть доступен процессу сервера и часто требуются права ОС.

COPY employees(id, firstname, lastname, email)
FROM '/tmp/sample.csv'
DELIMITER ','
CSV HEADER;

Когда использовать: файл уже находится на сервере, и у вас есть права читать этот файл процессом Postgres.

Важно: частые ошибки — неправильные права на файл, некорректная кодировка, несовпадающие типы колонок.

Импорт на стороне клиента

Если файл находится на вашей машине или у вас нет прав сервера, используйте psql и команду \copy. Она читает файл на клиенте и отправляет данные серверу.

\copy employees FROM '/tmp/sample.csv' DELIMITER ',' CSV HEADER;

Преимущество \copy — не требуется доступ сервера к локальной файловой системе.

Экспорт CSV из PostgreSQL

Аналогично импорту, экспорт возможен серверными и клиентскими командами.

Экспорт на стороне сервера

COPY employees TO '/tmp/employees.csv' CSV HEADER;

Для выборки части данных можно использовать COPY с SELECT:

COPY (SELECT * FROM employees WHERE firstname = 'Moyna') TO '/tmp/employees-moyna.csv' CSV HEADER;

Учтите ограничения прав: серверный COPY создаёт/записывает файл от имени процесса PostgreSQL.

Экспорт на стороне клиента

Через psql используйте \copy с ключом TO:

\copy employees TO '/home/user/employees.csv' CSV HEADER;

\copy сохранит файл на машине, где запущен psql.

Импорт через адаптер базы (на примере Python)

Иногда удобнее выполнять COPY из приложения. Для Python распространён адаптер psycopg2.

Установка:

pip install psycopg2

Пример подключения и выполнения серверного COPY (файл должен быть доступен PostgreSQL на сервере):

import psycopg2

connection = psycopg2.connect(
    database="employee_db",
    host="localhost",
    user="postgres",
    password="password",
)

cursor = connection.cursor()
cursor.execute("COPY employees(id, firstname, lastname, email) FROM '/tmp/sample.csv' DELIMITER ',' CSV HEADER;")
connection.commit()
cursor.close()
connection.close()

Альтернатива — отправлять данные клиентским способом, читая CSV в приложении и выполняя bulk insert через COPY FROM STDIN.

Частые ошибки и как их решать

• Ошибка прав доступа (permission denied): COPY серверная — файл должен быть читаем процессом postgres. Решение: разместите файл в каталоге с доступом или используйте \copy.
• Неправильная кодировка: Cyrillic/UTF-8 mismatch. Решение: привести файл к UTF-8 и использовать опцию ENCODING.
• Несовпадающие колонки/типы: порядок и количество колонок в CSV должны соответствовать списку в COPY или структуре таблицы.
• Проблемы с кавычками и разделителями: проверьте опции DELIMITER, QUOTE и ESCAPE.

Когда COPY не подходит

• Когда нужно валидация или сложная трансформация — лучше загружать в staging-таблицу и затем применять SQL-преобразования.
• Когда файл очень большой и нельзя держать его на сервере — используйте потоковую загрузку через STDIN/psycopg2 COPY FROM.
• При необходимости контролируемых транзакций на уровне каждой строки — выполняйте пакетные INSERT’ы с обработкой ошибок.

Шпаргалка: быстрое сравнение COPY и \copy

• COPY FROM ‘/path’ — выполняется сервером, требует доступ к файловой системе сервера.
• \copy FROM ‘/path’ — выполняется клиентом (psql), файл читается локально и отправляется серверу.
• Оба поддерживают CSV, DELIMITER, HEADER, QUOTE, NULL и ENCODING.

Чеклисты по ролям

DBA:

• Проверить права доступа к файлу для процесса postgres.
• Установить ожидаемую кодировку (ENCODING).
• Настроить безопасное размещение файлов и журналы.

Разработчик:

• Убедиться, что типы колонок совпадают с данными.
• Поддерживать тестовый CSV с краевыми кейсами (пустые строки, кавычки, запятые в полях).
• Автоматизировать загрузку через скрипты и CI.

Аналитик/BI:

• Проверить HEADER и формат дат/чисел.
• Загружать сначала в staging-таблицу и проверять статистики.
• Подготовить понятную схему для маппинга полей.

Полезные опции COPY (шпаргалка)

• DELIMITER ‘,’ — разделитель полей.
• CSV HEADER — первая строка — заголовок и не импортируется как данные.
• QUOTE ‘“‘ — символ, обрамляющий поля.
• NULL ‘NULL’ — представление NULL.
• ENCODING ‘UTF8’ — кодировка файла.

Пример с опциями:

COPY employees FROM '/tmp/sample.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', QUOTE '"', ENCODING 'UTF8');

Альтернативы и дополнительные подходы

• Использовать инструменты ETL (например, Apache NiFi, Airflow + кастомные скрипты) для регулярной загрузки и трансформации.
• При больших объёмах — партицирование таблиц и загрузка в параллели.
• Для ad-hoc — GUI-инструменты вроде pgAdmin ускоряют импорт/экспорт без командной строки.

Краткая методика безопасной загрузки

1. Подготовить CSV и проверить кодировку.
2. Создать staging-таблицу с простыми типами (всё varchar).
3. Выполнить загрузку в staging (COPY/\copy).
4. Применить валидации и преобразования SQL.
5. Перенести данные в целевые таблицы в транзакции.
6. Логи и откат: сохранять лог ошибок и иметь механизм удаления/отката.

Глоссарий (1 строка)

CSV — текстовый формат с запятыми как разделителями полей; COPY — PostgreSQL-команда для копирования данных между файлом и таблицей.

Заключение

CSV остаётся удобным форматом обмена данными с PostgreSQL. Выбирайте COPY или \copy в зависимости от расположения файла и прав. При автоматизации используйте адаптеры (psycopg2) или ETL-инструменты, а для разовых задач — pgAdmin. Всегда тестируйте на staging и следите за кодировкой и правами доступа.

Важно: перед массовой загрузкой сделайте резервную копию данных и подготовьте план отката.