PostgreSQL 23505: duplicate key violates unique constraint
An INSERT or UPDATE would create a second row with the same value in a column covered by a primary key, unique constraint or unique index.
- SQLSTATE
23505- Name
unique_violation- Category
- Integrity Constraint Violation
What it means
PostgreSQL enforces every primary key, UNIQUE constraint and unique index at the moment a row is written. If the new or changed row has the same key as a row that already exists (or one another transaction has written but not yet committed), the statement fails with 23505 and nothing it changed is kept.
The DETAIL line names the column and the duplicate value, and the message names the constraint or index, which tells you exactly which rule was broken.
Common causes
- Inserting a row whose key already exists, for example re-running an import or a seed script.
- A
serialor identity sequence that has fallen behind the data, usually after rows were loaded with explicit ids. The next default id collides with an existing row: the classicKey (id)=(1) already exists. - Two sessions inserting the same key at the same time; the second one waits for the first and fails as soon as the first commits.
- An
UPDATEthat sets a unique column to a value another row already has.
How to fix it
-
Find the existing row named in the
DETAILline and decide whether the new data is a duplicate or a different record.SELECT * FROM users WHERE email = '[email protected]';
-
If the write should update the existing row instead, make it an upsert with
ON CONFLICT.INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; -
If duplicates should simply be skipped, use
ON CONFLICT DO NOTHING.INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada') ON CONFLICT (email) DO NOTHING; -
If the duplicate is on a
serialor identity id, move the sequence past the highest existing value.SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT COALESCE(MAX(id), 0) + 1 FROM users), false);
Reproduce it safely
CREATE TEMP TABLE t (id int PRIMARY KEY); INSERT INTO t VALUES (1); INSERT INTO t VALUES (1); -- ERROR: duplicate key value violates unique constraint "t_pkey"
Questions
Why does the insert fail with duplicate key on the id column when I never set the id?
The table's sequence is behind the data, usually because rows were inserted with explicit ids or restored without the sequence value. Reset it with setval to one past MAX(id).
Does ON CONFLICT work with any unique index?
It works with the columns of a unique index or constraint, or with ON CONFLICT ON CONSTRAINT name. It needs PostgreSQL 9.5 or later.
Related errors
23503foreign key constraint violation23502null value violates not-null constraint25P02current transaction is aborted2627SQL Server: Violation of PRIMARY KEY or UNIQUE KEY2601SQL Server: Cannot insert duplicate key row1062MySQL: Duplicate entry for keyORA-00001Oracle: unique constraint violatedSQLITE_CONSTRAINT_UNIQUESQLite: UNIQUE constraint failed
More integrity constraint violation errors
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.