Обработка дублирующихся ключей при 'copy from' в PostgreSQL

Быстрый ответ

Чтобы игнорировать дубликаты при импорте данных в PostgreSQL через COPY FROM, создайте временную таблицу, а затем добавьте данные в основную таблицу, пропустив дубликаты с помощью ON CONFLICT DO NOTHING:

CREATE TEMP TABLE temp_table LIKE target_table;

COPY temp_table FROM 'source.csv' CSV;

INSERT INTO target_table
SELECT * FROM temp_table
ON CONFLICT DO NOTHING;

Этот подход гарантирует добавление только уникальных записей в целевую таблицу, избегая при этом конфликтов с ключами.

Удаляем дубликаты перед вставкой

Чистка данных от дубликатов перед их вставкой в основную таблицу – это вполне осмысленное решение. Хоть PostgreSQL и не предлагает встроенных ключевых слов 'IGNORE' или 'REPLACE', мы все же можем найти альтернативное решение:

CREATE TEMP TABLE temp_table AS SELECT * FROM main_table WITH NO DATA;

COPY temp_table FROM 'file.csv' CSV;

DELETE FROM temp_table
USING (
  SELECT MIN(ctid) as ctid, PK_field
  FROM temp_table 
  GROUP BY PK_field HAVING COUNT(*) > 1
) sub_query
WHERE temp_table.ctid != sub_query.ctid;

INSERT INTO main_table
SELECT * FROM temp_table
ON CONFLICT (PK_field) DO NOTHING;

Upsert приходит на помощь

В ситуациях, когда требуется обновить существующие записи, а не simplemente избегать дубликатов, используйте ON CONFLICT для выполнения операции upsert:

INSERT INTO main_table
SELECT * FROM temp_table
ON CONFLICT (PK_field) DO UPDATE SET
    column_1 = EXCLUDED.column_1,
    column_2 = EXCLUDED.column_2;

Такой подход означает: "Если возникает конфликт по первичному ключу, разрешим его, обновив существующую запись данными из временной таблицы".

Определение, что делать с дубликатами

Чтобы определить, какие дубликаты сохранить, используйте SELECT DISTINCT ON c сортировкой:

INSERT INTO main_table
SELECT DISTINCT ON (PK_field) *
FROM temp_table
ORDER BY PK_field, specific_column DESC;

Так, PostgreSQL сохранит запись с максимальным значением в specific_column для каждого дублированного PK_field.

Лучше перестраховаться

Предотвращение появления дубликатов в будущем – это вопрос тщательной организации процесса импорта и настроек ограничений таблицы:

• Рациональное использование уникальных ограничений и индексов.
• Регулярное обновление и очистка исходных данных.
• Использование инкрементных обновлений данных для минимизации дублирования.