duplicate key value violates unique constraint в PostgreSQL: причины и исправление

duplicate key value violates unique constraint означает, что INSERT или UPDATE создаёт ключ, который уже занят по правилам PRIMARY KEY, UNIQUE или уникального индекса. Код ошибки не зависит от языка сообщения: SQLSTATE 23505, условие называется unique_violation.

У ошибки четыре частые причины: значение действительно повторили, после импорта отстала sequence, два запроса одновременно вставляют один ключ или само ограничение описывает не то бизнес-правило. Удалять строку либо сбрасывать sequence наугад нельзя: сначала надо прочитать имя ограничения и DETAIL.

SQL / 01

23505: одинаковый код, разные причины

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

Воспроизводимый пример

Основные запросы ниже работают с таблицей accounts в PostgreSQL 18:

CREATE TABLE accounts (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  external_id text NOT NULL,
  email text NOT NULL,
  name text NOT NULL,
  CONSTRAINT accounts_email_key UNIQUE (email),
  CONSTRAINT accounts_tenant_external_key UNIQUE (tenant_id, external_id)
);

INSERT INTO accounts (tenant_id, external_id, email, name)
VALUES
  (10, 'crm-1', 'alice@example.com', 'Алиса'),
  (10, 'crm-2', 'boris@example.com', 'Борис');

Identity-колонка получила значения 1 и 2. Ограничение accounts_email_key требует, чтобы email не повторялся во всей таблице.

Как читать имя constraint и DETAIL

Повторим email Алисы:

INSERT INTO accounts (tenant_id, external_id, email, name)
VALUES (10, 'crm-3', 'alice@example.com', 'Другая Алиса');

ERROR:  duplicate key value violates unique constraint "accounts_email_key"
DETAIL: Key (email)=(alice@example.com) already exists.

Имя accounts_email_key указывает на нарушенное правило. DETAIL называет колонку и значение, с которыми база нашла конфликт. Сервер также передаёт SQLSTATE и имя ограничения отдельными полями; драйверы обычно открывают их как code = 23505 и constraint = accounts_email_key. В приложении надёжнее проверять эти поля, а не разбирать локализованный текст ошибки.

Определение именованного ограничения хранится в системном каталоге:

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'public.accounts'::regclass
  AND conname = 'accounts_email_key';

conname	definition
accounts_email_key	UNIQUE (email)

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

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'accounts'
  AND indexname = 'accounts_email_key';

После этого проверяют именно ключ из DETAIL, со всеми колонками составного ограничения:

SELECT id, tenant_id, external_id, email, name
FROM accounts
WHERE email = 'alice@example.com';

id	tenant_id	external_id	email	name
1	10	crm-1	alice@example.com	Алиса

Эта строка отвечает на главный вопрос: входной запрос повторяет ту же сущность или пытается создать другую. В первом случае нужен идемпотентный INSERT; во втором приложение должно отклонить занятый email либо бизнес-правило уникальности надо менять. Универсального безопасного DELETE здесь нет.

Причина 1. Значение действительно повторяется

Повтор возникает не только из-за двойного клика. Очередь может доставить сообщение ещё раз, клиент — повторить запрос после тайм-аута, а импорт — содержать одинаковые ключи. Ограничение в таком случае работает правильно и не даёт записать две строки.

Если повтор означает «запись уже создана», пропустите вставку явно:

INSERT INTO accounts (tenant_id, external_id, email, name)
VALUES (10, 'crm-99', 'alice@example.com', 'Алиса повторно')
ON CONFLICT (email) DO NOTHING
RETURNING id;

RETURNING вернёт ноль строк, если сработал DO NOTHING. Приложение может отдельно получить существующую запись по email. Явная цель (email) важна: она показывает, какой конфликт считается ожидаемым, а остальные ограничения продолжают сигнализировать об ошибке.

Если повтор должен обновить только разрешённые поля, используйте DO UPDATE:

INSERT INTO accounts (tenant_id, external_id, email, name)
VALUES (10, 'crm-1', 'alice@example.com', 'Алиса А.')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name
RETURNING id, email, name;

id	email	name
1	alice@example.com	Алиса А.

EXCLUDED содержит строку, которую пытались вставить. Не обновляйте из неё все колонки автоматически: список после SET должен отражать поля, которые разрешено менять при повторной доставке.

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

Причина 2. Sequence отстала после импорта

Identity и serial получают номера из отдельной sequence. Явная вставка id не передвигает её автоматически. Чтобы пример не зависел от значений, израсходованных предыдущими тестовыми вставками, воспроизведём импорт в отдельной таблице:

CREATE TABLE imported_accounts (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

INSERT INTO imported_accounts (email)
VALUES ('first@example.com'), ('second@example.com');

Sequence выдала id 1 и 2. Теперь импорт явно записывает следующие номера:

INSERT INTO imported_accounts (id, email)
VALUES
  (3, 'third@example.com'),
  (4, 'fourth@example.com');

Сначала найдём связанную sequence, затем сравним её состояние с данными:

SELECT pg_get_serial_sequence('public.imported_accounts', 'id') AS sequence_name;

sequence_name
public.imported_accounts_id_seq

SELECT max(id) AS max_id FROM imported_accounts;

SELECT last_value, increment_by
FROM pg_sequences
WHERE schemaname = 'public'
  AND sequencename = 'imported_accounts_id_seq';

max_id
4

last_value	increment_by
2	1

Следующий обычный INSERT получает из sequence число 3, хотя строка с таким id уже есть:

INSERT INTO imported_accounts (email)
VALUES ('fifth@example.com');

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

В этом примере sequence возрастает с шагом 1, поэтому после максимального id = 4 следующий номер равен 5. На работающей базе сначала остановите импорт и убедитесь, что краткая блокировка таблицы допустима:

BEGIN;
LOCK TABLE public.imported_accounts IN ACCESS EXCLUSIVE MODE;

SELECT max(id) + 1 AS next_id FROM public.imported_accounts;
-- Для этой таблицы результат: 5

ALTER SEQUENCE public.imported_accounts_id_seq RESTART WITH 5;
COMMIT;

Имя sequence берите из pg_get_serial_sequence, а число — из данных и её параметров. В примере используется стандартный CACHE 1; при большем кэше pg_sequences.last_value может опережать последнее выданное значение. Для пустой таблицы, отрицательного шага, CYCLE или шага, не равного единице, формула max(id) + 1 не подходит. ALTER SEQUENCE ... RESTART здесь предпочтительнее случайного setval: операция транзакционна и блокирует конкурентный nextval до завершения транзакции.

После исправления не ожидайте непрерывной нумерации. Sequence гарантирует безопасную выдачу значений нескольким сеансам, но пропуски остаются после откатов и даже после ON CONFLICT, потому что nextval вызывается до проверки конфликта.

Причина 3. Два INSERT выполняются одновременно

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

SELECT id FROM accounts WHERE email = 'race@example.com';
-- Сеанс A: 0 строк
-- Сеанс B: 0 строк

INSERT INTO accounts (tenant_id, external_id, email, name)
VALUES (30, 'race-1', 'race@example.com', 'Раиса');

Оба сеанса могут увидеть отсутствие строки, потому что SELECT читает снимок данных. Заблокировать отсутствующую строку через FOR UPDATE невозможно. Затем один INSERT запишет строку, а второй подождёт его завершения и получит 23505, если первая транзакция зафиксируется.

Убирает гонку один атомарный оператор:

INSERT INTO accounts (tenant_id, external_id, email, name)
VALUES (30, 'race-1', 'race@example.com', 'Раиса')
ON CONFLICT (email) DO NOTHING
RETURNING id;

Для сценария upsert замените действие на точечный DO UPDATE, как в предыдущем разделе. Отдельный SELECT можно оставить ради интерфейса, но считать его гарантией уникальности нельзя. Гарантию даёт уникальный индекс, а ожидаемый конфликт обрабатывает сам INSERT.

Причина 4. Неверно выбран unique constraint

В примере UNIQUE (email) запрещает один email во всех организациях. Если по требованиям email уникален только внутри организации, правильный ключ — (tenant_id, email). Повтор в другом tenant_id тогда не ошибка данных: неверна схема.

Сначала проверьте текущие данные и потребителей ключа. Особенно важны внешние ключи, которые могут ссылаться на старый UNIQUE (email). Не добавляйте CASCADE, если удаление ограничения блокируется зависимостью.

На большой таблице новое правило удобно подготовить без долгой блокировки записей:

CREATE UNIQUE INDEX CONCURRENTLY accounts_tenant_email_uidx
ON public.accounts (tenant_id, email);

Команда не выполняется внутри транзакционного блока. Если в данных уже есть повторы пары (tenant_id, email), создание индекса завершится ошибкой: такие строки надо разобрать по предметной области, а не удалять общим запросом.

Готовый индекс присоединяется как ограничение, затем старое правило удаляется:

ALTER TABLE public.accounts
ADD CONSTRAINT accounts_tenant_email_key
UNIQUE USING INDEX accounts_tenant_email_uidx;

ALTER TABLE public.accounts
DROP CONSTRAINT accounts_email_key;

После этого одинаковый email допустим в разных организациях, но не внутри одной. Обратный порядок оставил бы окно без нужной защиты. Подробнее о связи UNIQUE и B-tree рассказано в разборе индексов PostgreSQL.

Порядок диагностики 23505

Возьмите SQLSTATE 23505, имя ограничения и DETAIL из структурированных полей драйвера или сообщения PostgreSQL.
Получите определение ограничения из pg_constraint; для отдельного уникального индекса проверьте pg_indexes.
Найдите существующую строку по полному ключу из DETAIL и решите, действительно ли это одна сущность.
Если конфликтует автоинкрементный id, сравните max(id) с состоянием связанной sequence.
Если запросы могут приходить одновременно, перенесите ожидаемое разрешение конфликта в INSERT ... ON CONFLICT.
Меняйте ограничение только тогда, когда его область расходится с бизнес-правилом; сначала создайте новую защиту, затем снимайте старую.

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