PostgreSQL error: duplicate key value violates unique constraint

SQLSTATE 23505 (unique_violation) · Updated July 9, 2026

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

An INSERT or UPDATE tried to create a second row with the same value in a column (or set of columns) covered by a unique constraint or unique index. The DETAIL line tells you exactly which constraint and which value — the diagnosis starts from there.

What this error means

The constraint worked: PostgreSQL guarantees uniqueness even under concurrency, and this error is that guarantee firing. Two things worth knowing beyond the obvious:

• The name in quotes is the constraint or index name (users_email_key, users_pkey, …). \d users in psql shows which columns it covers — including expression indexes like lower(email), where the duplicate may not be literally identical.
• Like any error, it aborts the current transaction: every later statement in the same transaction will fail with "current transaction is aborted" until you roll back.

Common causes

• Check-then-insert races: two requests both run SELECT, both see no row, both INSERT. Under concurrency one of them must get 23505 — the check cannot fix this, only the insert itself can (see the upsert fix below).
• A sequence out of sync with the table — the classic after a data import or restore that inserted explicit IDs without advancing the sequence. New inserts draw already-used IDs and fail on the primary key, once per stale ID.
• Client retries replaying an INSERT that actually succeeded the first time (timeout after commit, at-least-once queues).
• Bulk loads containing genuine duplicates.

How to diagnose it

-- Quale vincolo, quali colonne:
\d users

-- Sospetto "sequenza rimasta indietro" (errore sulla _pkey con id seriali):
SELECT max(id) FROM users;
SELECT last_value FROM users_id_seq;
-- se last_value <= max(id), è questa la causa

If the constraint is on a business key (email, SKU, …) rather than the primary key, look at where the value in DETAIL comes from: same user double-submitting, retry logic, or two code paths creating the same entity.

How to fix it

• Insert races → upsert. Make the insert itself decide, atomically:

  INSERT INTO users (email, name)
  VALUES ('[email protected]', 'Alice')
  ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
  -- oppure DO NOTHING se il duplicato va semplicemente ignorato

• Stale sequence → resync it:

  SELECT setval(pg_get_serial_sequence('users', 'id'),
                (SELECT max(id) FROM users));

• Retries → idempotency: give operations a client-generated unique key and use ON CONFLICT DO NOTHING on it, so replays become no-ops.
• Bulk loads: load into a staging table, deduplicate (SELECT DISTINCT ON (key) ...), then insert.
• Avoid catch-and-ignore inside a larger transaction: the failed statement has already aborted it. Prefer ON CONFLICT; if you truly must attempt-and-recover, wrap the statement in a SAVEPOINT.