duplicate key value violates unique constraint

What it means

A PRIMARY KEY, UNIQUE constraint or unique index allows each value only once. Your INSERT or UPDATE would have created a second row with the same value, so the statement failed and none of its rows were written. The DETAIL line names the column and the clashing value:

DETAIL:  Key (email)=(ada@example.com) already exists.

Inside a transaction, the transaction is now aborted: every later statement fails with current transaction is aborted until you roll back.

Common causes

1. The sequence is behind the data. Rows were inserted with explicit ids (a CSV import, a copy from another database, a seed script, a hand-written INSERT), so the serial or identity sequence never moved. The next insert without an id gets a number that’s already taken. The clashing key is an id you didn’t supply, like Key (id)=(1).
2. A real duplicate: the same email signed up twice, a retried request, a job that ran twice.
3. An upsert written as a plain insert: the code means “insert or update” but only inserts.
4. An update that shifts values one by one, such as SET pos = pos + 1 on a unique column. PostgreSQL checks a non-deferrable unique constraint row by row, so the first row clashes with the next one before that one has moved.
5. Adding a unique constraint or index to data that already has duplicates. That fails with could not create unique index and the same error code.

How to fix it

Move the sequence past the highest id

Find the sequence behind the column and move it past the current maximum:

SELECT setval(pg_get_serial_sequence('plans', 'id'),
              coalesce(max(id), 0) + 1, false)
FROM plans;

With false as the third argument, the next nextval returns exactly that value, which also works on an empty table. This works for serial and identity columns. For an identity column you can also write ALTER TABLE plans ALTER COLUMN id RESTART WITH <n>.

Every failed insert still uses up a sequence number, because nextval is never rolled back. So retrying a few times eventually “works” once the sequence passes the existing ids: fix the sequence rather than relying on that. A pg_dump file sets each sequence’s position with setval; imports done any other way usually don’t.

Let the database assign ids

Leave the id column out of your INSERT lists. An identity column declared GENERATED ALWAYS refuses explicit ids unless you write OVERRIDING SYSTEM VALUE, which makes this mistake harder to make.

Write an upsert with ON CONFLICT

To ignore rows that already exist:

INSERT INTO customers (email, name) VALUES ('ada@example.com', 'Ada L.')
ON CONFLICT (email) DO NOTHING;

To update them instead:

INSERT INTO customers (email, name) VALUES ('ada@example.com', 'Ada L.')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

EXCLUDED is the row you tried to insert. The columns in ON CONFLICT (…) must match a unique constraint or unique index exactly; otherwise PostgreSQL says there is no unique or exclusion constraint matching the ON CONFLICT specification. If one INSERT contains the same key twice, DO UPDATE fails with ON CONFLICT DO UPDATE command cannot affect row a second time; remove duplicates from the input first.

Find existing duplicates

Before adding a unique constraint, or to see what clashes:

SELECT email, count(*)
FROM people
GROUP BY email
HAVING count(*) > 1;

Then delete or merge them, and add the constraint; see adding a unique constraint for doing that on a live table.

Make the constraint deferrable for shifting updates

For updates that move values along, such as reordering positions, declare the constraint DEFERRABLE INITIALLY IMMEDIATE. It’s then checked at the end of the statement instead of row by row:

ALTER TABLE steps DROP CONSTRAINT steps_pos_key,
  ADD CONSTRAINT steps_pos_key UNIQUE (pos) DEFERRABLE INITIALLY IMMEDIATE;

The PostgreSQL documentation warns this can be significantly slower than an immediate check.

Reproduce it

On PostgreSQL 18.6, an import that supplied its own ids, then a normal insert:

CREATE TABLE plans (id serial PRIMARY KEY, name text);
INSERT INTO plans (id, name) VALUES (1, 'free'), (2, 'pro');
INSERT INTO plans (name) VALUES ('team');

ERROR:  duplicate key value violates unique constraint "plans_pkey"
DETAIL:  Key (id)=(1) already exists.

After setval(pg_get_serial_sequence('plans', 'id'), coalesce(max(id), 0) + 1, false), the same insert returns id 3. A real duplicate on a unique column, and on a two-column constraint:

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

ERROR:  duplicate key value violates unique constraint "seats_row_no_seat_no_key"
DETAIL:  Key (row_no, seat_no)=(1, 1) already exists.

Shifting a unique column with rows at positions 1, 2 and 3:

UPDATE steps SET pos = pos + 1;

ERROR:  duplicate key value violates unique constraint "steps_pos_key"
DETAIL:  Key (pos)=(2) already exists.

The same update succeeds when the constraint is DEFERRABLE INITIALLY IMMEDIATE. Adding a unique index over existing duplicates:

ERROR:  could not create unique index "people_email_key"
DETAIL:  Key (email)=(a@example.com) is duplicated.

PostgreSQL 14, 15, 16 and 17 print the same messages.

In Inlet

Edits in the grid are staged until you commit (⌘S) and saved in one transaction, so a duplicate fails the whole save rather than leaving half of it written; Review shows the exact SQL first. In the query editor the error appears at the position the server reports, and with your own Anthropic API key Ask Claude (⌘L) can fix the failed statement, sending the schema, the SQL and the error but never rows.