Fixing "duplicate key value violates unique constraint" in Postgres

Posted by Kyle Hankinson July 17, 2026

---

You insert a row, let Postgres generate the ID, and get this back:

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

The confusing part is that you never supplied a duplicate. Postgres did. The sequence that hands out values for your SERIAL or identity column is pointing at a number that already exists in the table, almost always because rows were inserted with explicit IDs at some point: a bulk import, a pg_restore, an ETL job, a fixture loader, or a well-meaning script that copied data between environments.

Sequences in Postgres only advance when something calls nextval on them. An INSERT that supplies its own id value writes the row but leaves the sequence untouched. The next insert that relies on the default calls nextval, gets a stale number, and collides with a row that is already there.

Reproducing it in twenty seconds

Here is the whole failure, run on PostgreSQL 16. It behaves identically for identity columns (PostgreSQL 10 and later) and old-style SERIAL columns:

CREATE TABLE customers (
    id   bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

-- an "import" that supplies its own IDs
INSERT INTO customers (id, name)
VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Edsger');

-- a normal application insert
INSERT INTO customers (name) VALUES ('Linus');

On PostgreSQL 16 that last statement fails with exactly the error above: the sequence hands out 1, and row 1 already exists. Run it two more times and it fails on 2, then 3, then finally succeeds with 4. That staircase pattern (a few failures, then it starts working) is a reliable sign the sequence fell behind rather than anything actually being duplicated.

Confirm the diagnosis

First find the sequence. Don't guess at the name; ask Postgres:

SELECT pg_get_serial_sequence('customers', 'id');

This works for both identity and SERIAL columns and returns the schema-qualified name, here public.customers_id_seq. Now compare where the sequence is against where the data is:

SELECT last_value, is_called FROM customers_id_seq;
SELECT MAX(id) FROM customers;

On the broken table above, this is what comes back:

last_value	is_called	MAX(id)
1	t	3

If last_value is less than MAX(id), the sequence is behind and every default insert will keep colliding until it catches up. A client like SQLPro for Postgres makes this comparison quick since you can run both statements together and see the two result sets side by side, and the sequence itself is visible in the schema browser next to the table it feeds.

The fix

One statement resynchronizes the sequence with the table:

SELECT setval(
    pg_get_serial_sequence('customers', 'id'),
    COALESCE(MAX(id), 1),
    MAX(id) IS NOT NULL
) FROM customers;

After running this, INSERT INTO customers (name) VALUES ('Linus') succeeds and returns id 4. Verified on PostgreSQL 16.

The third argument deserves a closer look because most snippets floating around omit it. setval accepts an is_called flag: when true, the next nextval returns the stored value plus one; when false, it returns the stored value itself. MAX(id) IS NOT NULL evaluates to true for a table with rows (correct: the next ID should be max plus one) and false for an empty table (correct: the first ID should be 1, not 2).

This matters. Run the two-argument version on an empty table and you burn ID 1:

-- empty table, two-argument setval
SELECT setval(pg_get_serial_sequence('empty_tbl', 'id'), COALESCE(MAX(id), 1))
FROM empty_tbl;

INSERT INTO empty_tbl (v) VALUES ('a') RETURNING id;
-- returns 2, not 1

The three-argument version returns 1 for the same insert. Both behaviors verified on PostgreSQL 16. Skipping an ID is rarely harmful, but if you are resetting sequences you may as well reset them correctly. The full setval semantics are in the sequence functions documentation.

Fixing every sequence after a full import

After restoring a whole database from a dump that carried explicit IDs, you rarely want to fix tables one at a time. This block finds every column in public that is backed by a sequence and resynchronizes each one:

DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT quote_ident(t.relname) AS table_name,
               quote_ident(a.attname) AS column_name,
               pg_get_serial_sequence(quote_ident(n.nspname) || '.' || quote_ident(t.relname), a.attname) AS seq_name
        FROM pg_class t
        JOIN pg_namespace n ON n.oid = t.relnamespace
        JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum > 0 AND NOT a.attisdropped
        WHERE n.nspname = 'public'
          AND t.relkind = 'r'
          AND pg_get_serial_sequence(quote_ident(n.nspname) || '.' || quote_ident(t.relname), a.attname) IS NOT NULL
    LOOP
        EXECUTE format(
            'SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I), 1), (SELECT MAX(%I) IS NOT NULL FROM %I))',
            rec.seq_name, rec.column_name, rec.table_name, rec.column_name, rec.table_name);
    END LOOP;
END $$;

Verified on PostgreSQL 16: it picks up identity and SERIAL sequences alike and handles empty tables through the same three-argument setval. Adjust the nspname filter if your tables live outside public. For a broader look at resetting counters across different databases, see how to reset auto-increment and identity columns in SQL.

Why ON CONFLICT is not the answer

It is tempting to slap ON CONFLICT (id) DO NOTHING on the failing insert and move on. Don't. The IDs genuinely collide, so Postgres treats your new row as a duplicate of an old, unrelated row and silently discards it:

INSERT INTO customers (name) VALUES ('lost')
ON CONFLICT (id) DO NOTHING;
-- INSERT 0 0: the row is gone, no error, no data

Verified on PostgreSQL 16: the table still has three rows and 'lost' is nowhere. You have converted a loud, harmless error into silent data loss. ON CONFLICT is the right tool for real upserts against natural keys, which is a different problem covered in PostgreSQL upsert with INSERT ON CONFLICT.

Preventing the next occurrence

The cleanest prevention is to stop supplying explicit IDs during imports. Omit the ID column and let the sequence assign values; nothing ever drifts.

When you must preserve IDs (foreign keys reference them, or systems need to agree on identifiers), prefer GENERATED ALWAYS AS IDENTITY over SERIAL or GENERATED BY DEFAULT. It refuses accidental explicit IDs at insert time:

CREATE TABLE invoices (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount numeric
);

INSERT INTO invoices (id, amount) VALUES (100, 9.99);

On PostgreSQL 16 this fails immediately with:

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

An intentional import states its intent explicitly with INSERT ... OVERRIDING SYSTEM VALUE, and that explicitness is exactly the reminder you need to run the setval fix afterward. Identity columns are available from PostgreSQL 10 onward and are the recommended style in the identity columns documentation. Casual scripts that would have quietly desynchronized a SERIAL column fail loudly instead, and loud failures are the cheap kind.

---