unique_violation duplicate key value violates unique constraint “…” — 23505

Symptoms

An INSERT or UPDATE tried to store a key that already exists in a column (or set of columns) protected by a unique constraint or unique index. The DETAIL line shows exactly which key collided.

• The write is rejected and the transaction rolls back with SQLSTATE 23505.
• The DETAIL line names the constraint and the duplicate value.
• Common on retries, double-submits, or imports containing duplicates.

What the server log shows

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(ada@example.com) already exists.
STATEMENT:  INSERT INTO users (email) VALUES ('ada@example.com');

Why PostgreSQL raises this — what the manual says

Section 5.5.3 Unique Constraints:

“In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal.”

Every unique constraint is backed by a unique index. On write, PostgreSQL probes that index; if the new key already exists the write is rejected with 23505. NULLs are treated as distinct by default, so multiple NULLs are allowed unless the index uses NULLS NOT DISTINCT.

Common causes

• Inserting a row whose key already exists (duplicate email, username, etc.).
• A retried request that double-submits the same insert.
• A sequence reset behind existing data, so generated IDs collide.
• A data import containing duplicates.

How to fix it

1. Use INSERT ... ON CONFLICT (col) DO NOTHING or DO UPDATE for an upsert.
2. Check for an existing row first, or make the operation idempotent.
3. If a sequence drifted, fix it: SELECT setval('users_id_seq', (SELECT max(id) FROM users));.
4. De-duplicate import data before loading.

Diagnostic query

-- Find the duplicate values blocking the insert
SELECT email, count(*)
FROM users
GROUP BY email
HAVING count(*) > 1;

Swap in the column(s) from the constraint named in the DETAIL line.

Related & next steps

• SQLSTATE 23505 — Unique Violation (full reference)
• Message: conflicting key value violates exclusion constraint
• Message: null value violates not-null constraint

Reference: PostgreSQL 18 Section 5.4.3 “Unique Constraints”.