Incident brief
Duplicate key value violates unique constraint
A row was inserted with a value that a unique column already has. PostgreSQL blocked the insert so the unique constraint stays true.
In 10 seconds
- What
- Duplicate key value violates unique constraint
- What triggers it
- Create a table with a unique column, for example email.
- The fix
- Catch the error and tell the user that value is already taken.
- Proof
- Reproduced on PostgreSQL 16.14 → The second insert was rejected. Only the first row exists in the table afterward — no duplicate was created.
The fix
What to do right now
The immediate, application-level response to this error.
- Catch the error and tell the user that value is already taken.
- Use INSERT ... ON CONFLICT DO NOTHING (or DO UPDATE) instead of a bare INSERT.
- Keep the unique constraint. Do not replace it with an application-side check.
-- instead of a bare INSERT that can raise 23505:
INSERT INTO crm.contacts (id, email) VALUES (2, 'alice@example.com')
ON CONFLICT (email) DO NOTHING;Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — §5.4.3 Unique Constraints
Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. [...] 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.Read the full section on postgresql.org →
Step 1: insert the first row
The first insert has nothing to conflict with, so it succeeds and the unique index now has one entry: alice@example.com.Step 2: insert the duplicate row
PostgreSQL's own message names the exact constraint ("contacts_email_key") and the exact value that already exists. The insert is rejected before it ever changes the table — the unique index is what makes this check possible.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Create a table with a unique column, for example email.
- 2Insert one row with an email address.
- 3Insert a second row using that exact same email address.
- 4PostgreSQL rejects the second insert with SQLSTATE 23505.
One client, run as two sequential steps: insert the first row, then try to insert a duplicate.
DROP TABLE IF EXISTS crm.contacts;
CREATE TABLE crm.contacts (
id integer primary key,
email text unique
);INSERT INTO crm.contacts (id, email) VALUES (1, 'alice@example.com');-- second row tries to reuse the email that row 1 already has
INSERT INTO crm.contacts (id, email) VALUES (2, 'alice@example.com');What PostgreSQL actually returned
DROP TABLE
CREATE TABLEINSERT 0 1ERROR: duplicate key value violates unique constraint "contacts_email_key"
DETAIL: Key (email)=(alice@example.com) already exists.INSERT ... ON CONFLICT DO NOTHING was run against the same duplicate attempt, proving it avoids the error without needing to catch an exception in application code.
The exact same duplicate insert as the free reproduction above, resubmitted with an ON CONFLICT (email) DO NOTHING clause instead of a bare INSERT.
Without this
Above: a bare INSERT raised SQLSTATE 23505 and had to be caught.
With this, tested
Below: the same duplicate insert, with ON CONFLICT DO NOTHING — no error, no duplicate, table unchanged.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-15 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed