EC
23505
PostgreSQLERRORCommonIntegrity Constraint ViolationHIGH confidence

duplicate key value violates unique constraint

What this means

Postgres attempted to insert or update a row but found that the new value already exists in a column (or combination of columns) protected by a UNIQUE index. The executor checks the index before committing the heap write and aborts the statement when a duplicate is detected.

Why it happens
  1. 1Inserting a row with a primary key or unique column value that already exists in the table
  2. 2Updating a row so that its unique column value collides with another existing row
  3. 3Bulk-loading data (COPY, INSERT ... SELECT) that contains internal duplicates or conflicts with existing rows
  4. 4Race condition: two concurrent transactions both passed an application-level uniqueness check before either committed
How to reproduce

A table with a UNIQUE constraint on the email column receives a duplicate insert.

trigger — this will error
trigger — this will error
CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);

INSERT INTO users (email) VALUES ('alice@example.com');
INSERT INTO users (email) VALUES ('alice@example.com'); -- triggers 23505

expected output

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

Fix 1

Use ON CONFLICT DO NOTHING

WHEN When a duplicate should be silently skipped and the existing row left unchanged.

Use ON CONFLICT DO NOTHING
INSERT INTO users (email)
VALUES ('alice@example.com')
ON CONFLICT (email) DO NOTHING;

Why this works

The executor speculatively inserts the tuple into the heap and checks the unique index. When a conflict is detected it rolls back only that speculative insertion and returns 0 rows affected, without aborting the surrounding transaction. Introduced as "upsert" infrastructure in Postgres 9.5.

Fix 2

Use ON CONFLICT DO UPDATE (upsert)

WHEN When a duplicate should overwrite or merge specific columns of the existing row.

Use ON CONFLICT DO UPDATE (upsert)
INSERT INTO users (email, updated_at)
VALUES ('alice@example.com', NOW())
ON CONFLICT (email) DO UPDATE
  SET updated_at = EXCLUDED.updated_at;

Why this works

On conflict the executor fetches the conflicting heap tuple, applies the SET expressions using the EXCLUDED pseudo-table (which contains the proposed new values), and writes an updated tuple. The unique index is then re-checked on the updated values.

What not to do

✕ Drop the UNIQUE constraint to silence the error

Removes data integrity protection and allows genuine duplicate data to accumulate, corrupting application logic.

✕ Catch the error and retry blindly in a loop

Under high concurrency this creates a busy-wait loop; use ON CONFLICT instead which is handled atomically by the executor.

Version notes
Postgres 9.5+

ON CONFLICT (upsert) syntax introduced. Earlier versions require advisory locks or CTE-based workarounds.

Sources
Official documentation ↗

src/backend/executor/nodeModifyTable.c — ExecInsert()

INSERT ON CONFLICT documentation ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors