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.
What lands in your log
ERROR: duplicate key value violates unique constraint "contacts_email_key"
In 10 seconds
- What triggers it
- Create a table with a unique column, for example email.
- 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.
Fix
What to do right now
Application-level steps for 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, '[email protected]')
ON CONFLICT (email) DO NOTHING;For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Read the constraint named in DETAIL, then verify whether the application is racing an INSERT or replaying an already-accepted idempotency key.
Unique constraints and indexes on user tables
Map the constraint/index name from the error to its exact columns and predicate.
SELECT i.indexrelid::regclass AS index_name,
i.indrelid::regclass AS table_name,
i.indisprimary, i.indisunique,
pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index i
WHERE i.indisunique
ORDER BY i.indrelid::regclass::text, i.indexrelid::regclass::text
LIMIT 50;Measure the conflicting key
Replace the placeholders with the table/key printed in DETAIL; count must be 0 or 1 before choosing INSERT versus UPSERT.
SELECT <key_column>, count(*)
FROM <schema.table>
WHERE <key_column> = <value_from_detail>
GROUP BY <key_column>;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: [email protected].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.
CREATE SCHEMA IF NOT EXISTS crm;
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, '[email protected]');-- second row tries to reuse the email that row 1 already has
INSERT INTO crm.contacts (id, email) VALUES (2, '[email protected]');What PostgreSQL actually returned
DROP TABLE
CREATE TABLEINSERT 0 1ERROR: duplicate key value violates unique constraint "contacts_email_key"
DETAIL: Key (email)=([email protected]) 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.
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.
- A second operational test: exact SQL, raw output, measured result, and engineer notes
- Fix that looks safe but silently corrupts data: exact SQL, output, and verdict
- A manual-grounded production interpretation of the lab result
Card required. Cancel before day 7 and you are not charged.
Runbook to fix this
Runbooks for this incident
Full step-by-step fixes for the condition behind this error: the diagnosis, the exact SQL, and output captured in the lab.
- Guarantee exactly-once settlements with idempotency keysA retried or double-clicked payout writes two settlements and finance has to claw one back.Pro
- Recover from a failed CREATE INDEX CONCURRENTLYAn invalid index left behind by a failed build: reads ignore it, writes still pay for it.Pro
- Watch CREATE INDEX CONCURRENTLY progressCREATE INDEX CONCURRENTLY reports zero blocks done and someone is about to cancel it.Pro
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Understand the concept
Fix it — runbooks
Related errors
Current transaction is aborted, commands ignored until end of transaction blockInsert or update violates foreign key constraintNull value in column violates not-null constraintNew row violates exclusion constraintON CONFLICT DO UPDATE requires inference specification or constraint nameUnique constraint on partitioned tableSequence reached its maximum valueCannot insert a non-DEFAULT value into a GENERATED ALWAYS column
Verification
- Last verified
- 2026-07-15 (Docker lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.