BackendlyLab
PostgreSQL • Backend • DevOps

Ошибка «duplicate key value violates unique constraint» в PostgreSQL: причины и решения

Рано или поздно каждый пользователь баз данных сталкивается с ошибкой «duplicate key value violates unique constraint». Эта ошибка возникает во время INSERT или UPDATE и означает, что произошла попытка вставить строку со значением ключа, которое уже существует в таблице.

Как выглядит ошибка:

ERROR: duplicate key value violates unique constraint "key_name"
DETAIL: Key (id)=(999) already exists.

Чтобы исследовать ошибку, стоит обратиться к служебной таблице postrgeSQL - pg_constraint

Быстрый способ узнать колонку:

SELECT conname, pg_get_constraintdef(oid)
	FROM pg_constraint
WHERE conname = 'key_name';

Уникальность строк в таблице в PostgreSQL обеспечивается PRIMARY KEY (первичным ключом) и уникальными индексами (UNIQUE). Они могут быть:

  • одиночные — например (id), т.е. каждой записи соответствует уникальный идентификатор
  • составные — например (id_category, name), т.е. значение name может повторяться, но только в разных категориях.

Не зависимо от того INSERT или UPDATE вызвал ошибку, причины и способы её решения будут одинаковые.

Основные причины

1. Ошибка в логике приложения

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

  • Для массовых вставок стоит использовать ON CONFLICT.
  • При многопоточной работе важно корректно обрабатывать конфликт уникальности (например, через ON CONFLICT) или с помощью обработки ошибок try...catch
  • Можно дополнительно проверять уникальность до запроса к БД.

2. Конфликт при репликации или миграции

Данные могли дублироваться при переносе или синхронизации между базами.

3. Проблема с последовательностью (sequence)

При использовании SERIAL есть один важный момент, который легко можно упустить. Если вручную вставить значение в колонку, которая использует sequence, а потом попытаться вставить данные с автоинкрементом, то возникнет ошибка. Потому что последовательность не обновляется автоматически и для неё актуальное значение то, которое было вставлено в таблицу вручную. Надо самостоятельно изменить значение последовательности

Пример с последовательностью:

SELECT last_value FROM table_id_seq; -- получаем последнее, выданное sequence, значение (например 50)

INSERT INTO table (id, name)
VALUES (51, 'test');

INSERT INTO table (name)
VALUES ('test'); -- получим ошибку

SELECT last_value FROM table_id_seq; -- значение всё ещё 50

SELECT setval('table_id_seq', (SELECT MAX(id) FROM table)); -- Устанавливаем последовательность на правильное значение

4. Дублирование данных при вставке

Чаще всего ошибка встречается из-за неправильно подготовленных для вставки данных.

Если данные, указанные в ошибке, уже есть в таблице — значит их надо либо предварительно удалить, либо использовать ON CONFLICT


INSERT INTO table (name, age)
VALUES ('test', 25); -- если на поле name есть уникальный индекс, то получим ошибку т.к. есть запись с name = 'test' и age = 20

INSERT INTO table (name, age)
VALUES ('test', 25)
ON CONFLICT DO NOTHING -- ошибки не будет данные не изменятся
;

INSERT INTO table (name, age)
VALUES ('test', 25)
ON CONFLICT (name) DO UPDATE SET age = EXCLUDED.age -- ошибки не будет, age изменится на 25
;

Если указанных в ошибке данных в таблице нет - значит дубли отдаёт запрос, получивший данные для вставки. Надо либо исследовать и исправить запрос чтобы там не было дублей, либо использовать DISTINCT или DISTINCT ON, подойдёт также группировка с помощью GROUP BY и даже ON CONFLICT тут тоже сработает. Я отдаю предпочтение исправлению запроса. Только так можно гарантировать, что в таблице после выполнения будут те данные, которые мы ожидаем.


INSERT INTO table (id, name)
SELECT unnest(array[1, 1]) as id, unnest(array['test1', 'test2']) as name; -- получили ошибку т.к. дважды указан id = 1

INSERT INTO table (id, name)
SELECT DISTINCT ON (id) * FROM
(SELECT unnest(array[1, 1]) as id, unnest(array['test1', 'test2']) as name) t1; -- ошибки не будет но будет вставлена
только одна строка

Ошибка duplicate key value violates unique constraint — достаточно простая. Чаще всего она возникает по недосмотру. Чтобы её поправить достаточно определить какое ограничение (constraint) было нарушено и проверить существующие данные в таблице. Тогда станет понятно откуда берётся значение вызвавшее конфликт. В качестве простого и быстрого решения можно использовать ON CONFLICT, а для более точного исправления нужно или исправить запрос подготовивший данные и добавить обработку ошибок в приложении

Похожие статьи