// PostgreSQL error reference

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
Error message
duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

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 serial or 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 classic Key (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 UPDATE that sets a unique column to a value another row already has.

How to fix it

  1. Find the existing row named in the DETAIL line and decide whether the new data is a duplicate or a different record.

    SELECT * FROM users WHERE email = '[email protected]';
  2. 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;
  3. 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;
  4. If the duplicate is on a serial or 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

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.

// fewer errors, faster fixes

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.