// справочник ошибок PostgreSQL

PostgreSQL 23505: дублирующий ключ нарушает ограничение уникальности

INSERT или UPDATE создаст вторую строку с тем же значением в столбце, на который распространяется первичный ключ, уникальное ограничение или уникальный индекс.

SQLSTATE
23505
Имя
unique_violation
Категория
Нарушение ограничения целостности
Сообщение об ошибке
duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

Что это значит

PostgreSQL следит за каждым первичным ключом, UNIQUE ограничение и уникальный индекс в момент записи строки. Если новая или изменённая строка имеет тот же ключ, что и уже существующая строка (или та, которую написала другая транзакция, но ещё не зафиксировала), операция завершается с ошибкой 23505, и ничего не изменяется.

Этот DETAIL строка называет столбец и повторяющееся значение, а сообщение — ограничение или индекс, так что сразу ясно, какое правило нарушено.

Распространённые причины

  • Вставка строки, ключ которой уже существует, например повторный запуск импорта или скрипта заполнения данных.
  • A serial или последовательность identity, отставшую от данных, — обычно после загрузки строк с явно заданными id. Следующий id по умолчанию конфликтует с существующей строкой: классический Key (id)=(1) already exists.
  • Две сессии вставляют один и тот же ключ одновременно; вторая ожидает первую и завершается с ошибкой, как только первая подтверждает изменения.
  • Вот UPDATE который ставит в уникальный столбец значение, которое уже есть у другой строки.

Как исправить

  1. Найдите существующую строку, указанную в DETAIL строке и решите, являются ли новые данные дубликатом или другой записью.

    SELECT * FROM users WHERE email = '[email protected]';
  2. Если запись должна вместо этого обновить существующую строку, превратите её в upsert с помощью ON CONFLICT.

    INSERT INTO users (email, name)
    VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
  3. Если дубликаты следует просто пропускать, используйте ON CONFLICT DO NOTHING.

    INSERT INTO users (email, name)
    VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO NOTHING;
  4. Если дубликат находится на serial или identity-id, сдвиньте последовательность за максимальное существующее значение.

    SELECT setval(pg_get_serial_sequence('users', 'id'),
                  (SELECT COALESCE(MAX(id), 0) + 1 FROM users), false);

Безопасное воспроизведение

CREATE TEMP TABLE t (id int PRIMARY KEY);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (1);  -- ERROR: duplicate key value violates unique constraint "t_pkey"

Вопросы

Почему вставка завершается ошибкой дубликата ключа в столбце id, если я не задавал id?

Последовательность таблицы отстаёт от данных — обычно строки вставлялись с явными id или восстанавливались без значения последовательности. Сбросьте её с помощью setval на единицу дальше MAX(id).

Работает ли ON CONFLICT с любым уникальным индексом?

Он работает со столбцами уникального индекса или ограничения, или с ON CONFLICT ON CONSTRAINT name. Требуется PostgreSQL 9.5 или новее.

Связанные ошибки

Больше ошибок нарушения ограничений целостности

Код и текст сообщения взяты из Документация PostgreSQL, приложение A: коды ошибок PostgreSQL. Смотрите весь Список ошибок PostgreSQL или поиск по всем ошибкам баз данных.

// меньше ошибок, быстрее исправления

Работа с PostgreSQL в SQLly

Нативный SQL-клиент для macOS, Windows и Linux с IntelliSense, который знает вашу схему, предохранителями для рискованных запросов и понятными сообщениями об ошибках — для PostgreSQL и других баз данных, которые вы используете.