COPY в PostgreSQL 18: ограничиваем число плохих строк

6 мин чтения

Как импортировать CSV без бесконтрольного пропуска ошибок: ON_ERROR, REJECT_LIMIT, подробные NOTICE, таблица pg_stat_progress_copy и проверка результата.

Импорт отделяет допустимые строки от ошибок преобразования и останавливается, если качество файла выходит за заданный предел.
  1. CSV на клиентеpsql читает локальный файл командой \copy и передаёт данные серверу через COPY FROM STDIN.
  2. ПреобразованиеPostgreSQL разбирает CSV и приводит значения к типам столбцов.
  3. Допустимая строкаСтрока проходит ограничения таблицы и добавляется в промежуточную таблицу.
  4. Ошибка типаON_ERROR ignore пропускает строку, увеличивает tuples_skipped и выводит NOTICE.
  5. Порог качестваЕсли число ошибок превышает REJECT_LIMIT, вся команда завершается ошибкой.

Обычный COPY FROM прекращает импорт при первой ошибке. Для строгой загрузки это правильное поведение, но большой CSV от внешней системы иногда полезнее проверить целиком: принять корректные строки, увидеть проблемные позиции и остановиться, если дефектов слишком много.

В PostgreSQL 17 появился режим ON_ERROR = ignore, однако без ограничения он способен пропустить сколько угодно строк. PostgreSQL 18 добавил REJECT_LIMIT: теперь допустимый объём ошибок задаётся прямо в команде. Это не «починка» входных данных, а контролируемая граница качества.

Сначала проверяем версию и область действия

SHOW server_version;
SHOW server_version_num;

Для примеров с REJECT_LIMIT нужен сервер PostgreSQL 18 или новее: значение server_version_num начинается с 180000. Ветка 18 остаётся актуальной поддерживаемой версией, а параметр отмечен в её release notes как отдельное изменение COPY.

ON_ERROR ignore обрабатывает только ошибки преобразования входного текста в тип столбца: например, строку not-a-date для timestamptz или wrong для numeric. Нарушение NOT NULL, CHECK, внешнего ключа либо ошибка триггера не превращается автоматически в пропуск строки. Эти ограничения остаются контрактом таблицы; подробнее о различии CHECK и NOT NULL.

Готовим промежуточную таблицу и тестовый CSV

Импортировать внешний файл сразу в рабочую таблицу неудобно: даже корректные по типам данные могут требовать проверки бизнес-правил. Поэтому сначала используем отдельную staging-таблицу.

CREATE TABLE public.measurement_stage (
    sample_code text NOT NULL,
    measured_at timestamptz NOT NULL,
    value numeric(12, 4) NOT NULL
);

Пример файла measurements.csv:

sample_code,measured_at,value
S-100,2026-11-16T08:30:00Z,12.4500
S-101,not-a-date,11.9000
S-102,2026-11-16T08:32:00Z,wrong
S-103,2026-11-16T08:33:00Z,12.7300

Здесь две строки корректны, одна содержит ошибочную дату, ещё одна — нечисловое значение. Файл не содержит реальных идентификаторов, адресов или секретов.

Импортируем через \copy с пределом ошибок

\copy public.measurement_stage (sample_code, measured_at, value)
FROM './measurements.csv'
WITH (
    FORMAT csv,
    HEADER true,
    ON_ERROR ignore,
    REJECT_LIMIT 2,
    LOG_VERBOSITY verbose
);

Команда \copy относится к клиенту psql: файл открывает пользователь, запустивший psql, а данные передаются серверу как COPY FROM STDIN. Это безопаснее и понятнее для локального файла, чем SQL-команда COPY ... FROM '/path', где путь относится к машине сервера и требуются серверные права на чтение.

Что делают параметры

  • ON_ERROR ignore пропускает строку, если значение не удалось преобразовать к типу соответствующего столбца.
  • REJECT_LIMIT 2 разрешает не более двух таких ошибок. Третья завершит всю команду ошибкой.
  • LOG_VERBOSITY verbose выводит NOTICE с номером строки файла и именем столбца для каждого пропуска.
  • HEADER true отбрасывает первую строку. Если нужно проверить названия и порядок колонок, в PostgreSQL 18 можно использовать HEADER MATCH.

Без REJECT_LIMIT режим ignore допускает неограниченное количество ошибок. Для автоматической загрузки это опасно: команда может формально завершиться, хотя полезных строк почти не осталось.

Наблюдаем импорт в pg_stat_progress_copy

Пока загрузка выполняется, из другого соединения можно посмотреть её состояние:

SELECT
    pid,
    relid::regclass AS target_table,
    command,
    type,
    bytes_processed,
    bytes_total,
    CASE
        WHEN bytes_total > 0
        THEN round(100.0 * bytes_processed / bytes_total, 1)
    END AS percent,
    tuples_processed,
    tuples_skipped
FROM pg_stat_progress_copy
ORDER BY pid;

tuples_skipped растёт при строках, отброшенных из-за ON_ERROR. Для \copy источник обычно отображается как PIPE, а bytes_total может быть равен нулю: клиент передаёт поток, и сервер не всегда знает его полный размер. Поэтому процент рассчитан только при известном объёме; счётчики строк остаются полезными и без него.

Строка исчезает из представления сразу после завершения COPY. Если импорт короткий, наблюдение лучше выполнять на реальном большом файле или собирать состояние штатным мониторингом, а не добавлять искусственные задержки.

Проверяем результат после завершения

SELECT count(*) AS accepted_rows
FROM public.measurement_stage;

SELECT sample_code, measured_at, value
FROM public.measurement_stage
ORDER BY sample_code;

Для приведённого примера ожидаются две принятые строки: S-100 и S-103. Это следует из содержимого файла и заданного лимита, а не является заявлением о выполненном тесте на конкретной инфраструктуре. Командный тег COPY n показывает число загруженных строк, а итоговый NOTICE — число пропущенных.

После проверки данные обычно переносят в рабочую таблицу отдельной транзакцией: применяют дополнительные условия, разрешают конфликты по явному ключу и записывают идентификатор партии. Для повторяемой загрузки пригодится подход из статьи «UPSERT для счётчика: конфликт должен иметь явный ключ».

Диагностика типичных проблем

REJECT_LIMIT не распознан

Сначала проверьте server_version_num, а не версию утилиты psql. Параметр разбирает сервер; подключение к PostgreSQL 17 или более старой версии объясняет ошибку даже при новом клиенте.

Файл не найден

Уточните, какая команда используется. В \copy относительный путь считается от текущего каталога клиента. В SQL-команде COPY FROM '/path' файл ищется на сервере базы данных и должен быть доступен системному пользователю PostgreSQL.

Импорт остановился, хотя указан ON_ERROR ignore

Посмотрите тип ошибки. Режим ignore не скрывает нарушение ограничения, ошибку триггера, неверную структуру CSV или проблему кодировки. Он предназначен для ошибок преобразования отдельных полей. Такая узкая область действия не позволяет случайно превратить все ошибки базы в «плохие строки».

Превышен REJECT_LIMIT

Если число ошибок стало больше лимита, команда завершается неуспешно, а вставленные ею строки не становятся видимыми. Однако документация предупреждает: при поздней ошибке физически записанные версии строк могут остаться как невидимые dead tuples и занять место до VACUUM. На больших загрузках поэтому разумно использовать отдельную staging-таблицу и контролировать её размер. Наблюдение за очисткой разобрано в статье «Autovacuum в PostgreSQL: наблюдаем прежде, чем менять настройки».

Когда нужен другой маршрут

ON_ERROR ignore не сохраняет полное содержимое отвергнутых строк в отдельную таблицу. Если требуется аудит каждой исходной записи, лучше сначала загрузить все поля как text в raw-таблицу вместе с номером партии, а затем проверять и преобразовывать их отдельными запросами. Такой процесс длиннее, зато позволяет показать оператору исходное значение, причину отказа и результат повторной обработки.

Для обычного контролируемого CSV достаточно более короткой схемы: staging-таблица, ON_ERROR ignore, небольшой REJECT_LIMIT, подробные сообщения на этапе настройки и проверка количества строк перед переносом. Так импорт продолжает работу при единичном дефекте, но не выдаёт испорченный файл за успешную загрузку.

Источники и дальнейшее чтение