23505ERRORTier 1 — Safe✅ HIGH confidence

duplicate key value violates unique constraint

Category: Integrity Constraint ViolationVersions: All Postgres versions

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

1Inserting a row with a primary key or unique column value that already exists in the table
2Updating a row so that its unique column value collides with another existing row
3Bulk-loading data (COPY, INSERT ... SELECT) that contains internal duplicates or conflicts with existing rows
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

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

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 a duplicate should be silently skipped and the existing row left unchanged.

fix

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 a duplicate should overwrite or merge specific columns of the existing row.

fix

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

Why it's wrong: Removes data integrity protection and allows genuine duplicate data to accumulate, corrupting application logic.

✗

Catch the error and retry blindly in a loop

Why it's wrong: 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 docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

🔧 Source ref: src/backend/executor/nodeModifyTable.c — ExecInsert()

📖 Further reading: INSERT ON CONFLICT documentation

Confidence assessment

✅ HIGH confidence

Behaviour is stable and well-documented across all supported versions. The ON CONFLICT mechanism is specified in SQL:2003 and Postgres follows it closely. Edge case: deferred unique constraints do not fire until end of transaction, so 23505 may appear later than expected.