Импорт и экспорт данных PostgreSQL — COPY, CSV, encoding

COPY — самый быстрый способ загрузить данные в PostgreSQL. Разбираем отличие COPY от \copy, импорт CSV с заголовками и проблемами кодировок, экспорт результатов запросов и работу с большими файлами.

COPY vs \copy — в чём разница

-- COPY (серверная, нужен суперпользователь):
COPY users (email, username, age)
FROM '/var/lib/postgresql/users.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- \copy (psql клиент, файл на вашей машине):
\copy users (email, username, age) FROM 'users.csv' CSV HEADER

-- Разница только в команде (\) и пути к файлу
-- \copy автоматически передаёт файл через соединение psql

Импорт CSV — основные варианты

-- Базовый импорт CSV с заголовками:
\copy users FROM 'users.csv' CSV HEADER

-- Указать разделитель (например точка с запятой):
\copy users FROM 'users.csv' CSV HEADER DELIMITER ';'

-- Указать NULL значения (пустая строка → NULL):
\copy users FROM 'users.csv' CSV HEADER NULL ''

-- Импортировать только нужные колонки:
\copy users (email, username) FROM 'users.csv' CSV HEADER

-- Импортировать с обработкой ошибок (PostgreSQL 17+):
COPY users FROM '/path/to/users.csv'
WITH (FORMAT csv, HEADER true, ON_ERROR IGNORE, LOG_VERBOSITY VERBOSE);

-- Импорт через STDIN (удобно в скриптах):
cat users.csv | psql -U postgres -d mydb -c "\copy users FROM STDIN CSV HEADER"

Кодировки — UTF-8 и Windows-1251

-- Проблема: файл в Windows-1251 (русские буквы), база в UTF-8
-- Ошибка: invalid byte sequence for encoding "UTF8"

-- Решение 1: указать кодировку файла в COPY:
COPY users FROM '/path/to/file.csv'
WITH (FORMAT csv, HEADER true, ENCODING 'WIN1251');

-- \copy с кодировкой:
\copy users FROM 'users_win1251.csv' CSV HEADER ENCODING 'WIN1251'

-- Решение 2: конвертировать файл перед импортом:
# Linux:
iconv -f WINDOWS-1251 -t UTF-8 users_win1251.csv > users_utf8.csv
\copy users FROM 'users_utf8.csv' CSV HEADER

# Windows PowerShell:
# Get-Content users.csv -Encoding Default | Set-Content users_utf8.csv -Encoding UTF8

-- Проверить кодировку базы данных:
SHOW client_encoding;
\l  -- в колонке Encoding увидите UTF8

-- Принудительно установить кодировку клиента:
SET client_encoding = 'WIN1251';
\copy users FROM 'users.csv' CSV HEADER  -- PostgreSQL сам сконвертирует

Экспорт данных в CSV

-- Экспортировать всю таблицу:
\copy users TO 'users_export.csv' CSV HEADER

-- Экспортировать результат запроса:
\copy (SELECT id, email, created_at FROM users WHERE active = TRUE) \
  TO 'active_users.csv' CSV HEADER

-- Экспорт с разделителем ;:
\copy users TO 'users.csv' CSV HEADER DELIMITER ';'

-- Экспорт с кавычками для всех полей:
\copy users TO 'users.csv' CSV HEADER FORCE QUOTE *

-- Экспорт в кодировке Windows-1251 (для Excel):
\copy users TO 'users_excel.csv' CSV HEADER ENCODING 'WIN1251'

-- Серверная COPY — экспорт на сервере:
COPY users TO '/var/lib/postgresql/users_export.csv'
WITH (FORMAT csv, HEADER true);

-- Экспорт через psql без входа в интерактивный режим:
psql -U postgres -d mydb -c "\copy users TO 'export.csv' CSV HEADER"

Импорт из других форматов

-- Импорт TSV (Tab-separated values):
\copy users FROM 'users.tsv' DELIMITER E'\t'
-- E'\t' — escape-последовательность для символа табуляции

-- Импорт из JSON через промежуточную таблицу:
CREATE TEMP TABLE json_import (data JSONB);
\copy json_import FROM 'users.jsonl'

INSERT INTO users (email, username, age)
SELECT
  data->>'email',
  data->>'username',
  (data->>'age')::INTEGER
FROM json_import;

DROP TABLE json_import;

-- Импорт SQL-дампа (не через COPY, через psql):
psql -U postgres mydb < dump.sql

-- Работа с большими файлами — загрузка по частям:
split -l 100000 big_file.csv chunk_
for f in chunk_*; do
  \copy users FROM "$f" CSV HEADER
  echo "Imported $f"
done

Часто задаваемые вопросы

Почему COPY быстрее чем INSERT в PostgreSQL?

COPY работает в режиме bulk-загрузки: отключает построчную проверку, использует меньше WAL-записей, обходит часть overhead’а триггеров и ограничений (можно явно отключить через SET session_replication_role = replica;). На практике COPY в 10-100 раз быстрее одиночных INSERT для больших объёмов. Множественный INSERT INSERT INTO t VALUES (...),(...) тоже быстрее одиночных, но всё равно медленнее COPY. Для миллионов строк: временно удалить индексы, загрузить через COPY, создать индексы заново.

Как импортировать CSV если в значениях есть запятые или переносы строк?

CSV-стандарт предусматривает кавычки: значения с запятыми или переносами строк должны быть обёрнуты в двойные кавычки: "значение, с запятой". Если в значении есть кавычка — она удваивается: "он сказал ""привет""". PostgreSQL COPY корректно обрабатывает стандартный CSV. Убедитесь что файл экспортирован корректно — Excel при сохранении в CSV автоматически добавляет кавычки где нужно. Если файл некорректный — используйте Python с csv.reader для предобработки перед импортом.

Как открыть экспорт из PostgreSQL в Excel с русскими буквами?

Excel по умолчанию открывает CSV в кодировке Windows-1251 (на русских системах). При экспорте из PostgreSQL (UTF-8) русские буквы будут искажены. Решение: экспортировать в Windows-1251: \copy (SELECT ...) TO 'export.csv' CSV HEADER ENCODING 'WIN1251'. Если нужен UTF-8: в Excel использовать «Открыть» → «Из текста/CSV» → выбрать кодировку UTF-8. Или добавить BOM: echo -ne '\xEF\xBB\xBF' > export_with_bom.csv && cat export.csv >> export_with_bom.csv — Excel автоматически определит UTF-8 при наличии BOM.