PostgreSQL: duplicate key value violates unique constraint

ERROR: duplicate key value violates unique constraint "invoices_pkey" (SQLSTATE 23505) means an insert or update would put a value in a unique index that is already there. When the constraint is the primary key and the insert does not name an id, the cause is almost always a sequence that is behind the table: rows went in with explicit ids — an import, a COPY, a data-only restore — and the counter that hands out new ids never moved. The fix is one setval call, and it has two traps: an empty table and the difference between max(id) and max(id) + 1.

We reproduced every case below on PostgreSQL 17.11 and 18.6, which behaved identically.

Why does an insert fail on the primary key when it does not set an id?

Because the sequence behind serial or generated by default as identity only advances when it is asked for a number. We created a table with id serial primary key, inserted three rows with ids 1, 2 and 3, and then inserted a row without an id:

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

The sequence still read last_value = 1, is_called = f: it had never been used. The next attempt failed on id 2, the one after on 3, and the fourth insert succeeded with id 4. Each failed insert uses up a number, so retries do get past the block of taken ids directly above the counter — and then fail again at the next imported id. With ids 1, 2, 3, 10 and 11 imported, three inserts failed, the next five got 4 to 9, and the one after that failed on 10. One insert getting through does not mean the sequence is fixed; only setval does. An identity column generated by default behaved exactly the same.

generated always as identity stops the plain insert: an explicit id gave 428C9: cannot insert a non-DEFAULT value into column "id" with the hint Use OVERRIDING SYSTEM VALUE to override. With OVERRIDING SYSTEM VALUE the rows went in and the next insert failed on id 1 just the same. COPY with an id column needed no override at all: COPY 1, and the counter stayed where it was.

How do you reset the sequence after an import?

select setval(pg_get_serial_sequence('t', 'id'), coalesce(max(id), 0) + 1, false) from t;

pg_get_serial_sequence finds the sequence for both serial and identity columns (it returned public.t_ident_id_seq for an identity column too) — but only a sequence the column owns. In older schemas the default is often a hand-written default nextval('t_id_seq') without OWNED BY; then pg_get_serial_sequence returns NULL, setval(NULL, …) returns NULL without an error, and the next insert fails as before. Read the sequence name from the column default (\d t) and pass it directly. We measured the variants people copy from forum answers:

max(id) and max(id) + 1, false lead to the same next id, because the two-argument form sets is_called to true; the documentation puts it as “the next nextval will advance the sequence before returning a value”. The difference is the empty table, where only the + 1, false form does something sensible. restart without a number goes back to the column’s start value, which is how a migration can re-create the problem it meant to fix. The same happens when you add an identity to a table that had no default at all — the case behind null value in column “id” violates not-null constraint.

Two properties matter if you run this on a live database. setval is not undone by a rollback: we called setval(…, 100) and nextval inside a transaction, rolled back, and the sequence read 101, t — the documentation says changes made by setval “are immediately visible to other transactions, and are not undone if the calling transaction rolls back”. And numbers are never given back: a failed insert used id 3, a rolled-back insert used id 5, and the table ended up with 1, 2, 3, 4, 6.

Which imports leave the sequence behind?

pg_dump carries the counter along, which is what you want for a whole database and the opposite of what you want when you restore one tenant’s rows into a live shared table: there the setval from the dump moves the shared counter back, and every tenant’s inserts fail until it has caught up again. That case, with numbers, is in dumping one tenant’s data with pg_dump.

How do you find every sequence that is behind?

One query, over all tables whose column owns a sequence (serial has dependency type a, identity i), compares max(id) with the next value the sequence would hand out:

with owned as (
  select n.nspname as schema, c.relname as tbl, a.attname as col,
         s.oid::regclass as seq, sq.seqincrement as inc
  from pg_depend d
  join pg_class s     on s.oid = d.objid and s.relkind = 'S'
  join pg_sequence sq on sq.seqrelid = s.oid
  join pg_class c     on c.oid = d.refobjid
  join pg_namespace n on n.oid = c.relnamespace
  join pg_attribute a on a.attrelid = c.oid and a.attnum = d.refobjsubid
  where d.classid = 'pg_class'::regclass and d.refclassid = 'pg_class'::regclass
    and d.deptype in ('a', 'i')
    and n.nspname not in ('pg_catalog', 'information_schema')
), vals as (
  select o.*,
    (xpath('/row/m/text()', query_to_xml(format('select max(%I) as m from %I.%I', col, schema, tbl), false, true, '')))[1]::text::bigint as max_id,
    (xpath('/row/l/text()', query_to_xml(format('select last_value as l from %s', seq), false, true, '')))[1]::text::bigint as last_value,
    (xpath('/row/c/text()', query_to_xml(format('select is_called as c from %s', seq), false, true, '')))[1]::text::boolean as is_called
  from owned o
)
select schema, tbl, col, max_id,
       case when is_called then last_value + inc else last_value end as next_value,
       format('select setval(%L, %s, false);', seq::text, coalesce(max_id, 0) + 1) as fix
from vals
where max_id is not null
  and (case when is_called then last_value + inc else last_value end) <= max_id;

query_to_xml lets one statement run a max() per table without a function. On a test database with three tables it listed two:

 schema |    tbl     | col | max_id | next_value |                       fix
--------+------------+-----+--------+------------+-------------------------------------------------
 public | bestelling | id  |    120 |          1 | select setval('bestelling_id_seq', 121, false);
 public | factuur    | id  |     50 |         11 | select setval('factuur_id_seq', 51, false);

After running the two generated setval lines, the query returned nothing. Its limits, measured: it only sees sequences owned by a column, so a sequence used through a plain default nextval(…) is not listed; it assumes ascending sequences (with a negative increment it flagged a healthy sequence and proposed an out-of-range setval, and with an increment of 10 the proposed value breaks the step); and it reads max() from every table, so run it outside busy hours, as a role that can read every table and sequence (one missing grant aborted the whole query) and that bypasses row-level security — under a policy, max(id) only saw the tenant’s own rows and the table silently dropped out of the list.

What if the constraint is not the primary key?

Two sessions inserting the same key. With unique (tenant_id, email), session 1 inserted (1, 'a@x.nl') and kept its transaction open for five seconds; session 2, started a second later, inserted the same pair. It did not fail at once: it waited (wait_event = transactionid) for about 4.0 seconds, until session 1 finished. When session 1 committed, session 2 got Key (tenant_id, email)=(1, a@x.nl) already exists.; when session 1 rolled back, session 2’s insert succeeded. on conflict do nothing waited just as long and then returned INSERT 0 0; on conflict (tenant_id, email) do update waited and then updated session 1’s row. So a 23505 on a natural key under load is a real duplicate that arrived at the same time, and a long transaction in front of it shows up as a slow insert first.

An upsert aimed at the wrong constraint. On a table with id as primary key and a unique email, insert … values (2, 'a@x.nl') on conflict (id) do nothing still failed with duplicate key value violates unique constraint "g_email_key", and so did do update. ON CONFLICT (id) only handles conflicts on id. on conflict do nothing without a target caught it (INSERT 0 0), but do update needs a target (42601 without one), so an upsert on a table with two unique keys has to pick the one it means. How per-tenant unique keys should be shaped in the first place is in unique per tenant in PostgreSQL.

NULLs. A plain unique constraint on (tenant_id, email) accepted two rows (null, 'a@x.nl'), because NULLs are not equal to each other. The same constraint declared unique nulls not distinct (PostgreSQL 15 and later) refused the second one with Key (tenant_id, email)=(null, a@x.nl) already exists. Adding such an index to a table that already had the two rows failed with could not create unique index … is duplicated.

Under row-level security, or without SELECT, the DETAIL line is missing. As a role that does not bypass row-level security, on a table with a policy using (true), the same primary-key conflict gave the constraint, schema and table, and no DETAIL line at all: The same happened without row-level security to a role with INSERT but no SELECT on the table; with SELECT granted, the DETAIL came back. PostgreSQL leaves out the key value when the role may not see it. In a multi-tenant application that is the usual case, so the diagnosis above has to come from the constraint name, and for a _pkey, from comparing max(id) with the sequence as a role that sees every row.

Do not silence it with on conflict do nothing. With a sequence behind, insert into t (naam) values ('new') on conflict do nothing — with or without (id) — answered INSERT 0 0, and the new row was simply not there. The error was gone and so was the data.

What we did not measure

Sequences with cache greater than 1, where each session reserves a block of numbers. Logical replication, which does not replicate sequence values; what that does after a cut-over is in moving one tenant with logical replication. How specific ORMs report the error or retry.

Sources: PostgreSQL 17 documentation on sequence manipulation functions (setval, is_called, gaps), read on 21 October 2026. All measurements are our own, on PostgreSQL 17.11 and 18.6 in containers, on 21 October 2026.