Postgres duplicate key value violates unique constraint pkey after bulk import

We merged the helpdesk of a company we had acquired into our own support platform over a weekend. The plan was to keep their ticket numbers, because customers quote them in emails, so we imported their 410,000 tickets with the original ids into a new tickets table in our schema. Validation queries looked perfect. On Monday at 08:00, when agents started creating tickets, the Go API began returning 500s on POST /tickets:

ERROR: duplicate key value violates unique constraint "tickets_pkey" (SQLSTATE 23505)

The PostgreSQL log had the detail:

ERROR:  duplicate key value violates unique constraint "tickets_pkey"
DETAIL:  Key (id)=(1043) already exists.
STATEMENT:  INSERT INTO tickets (subject, requester_id, status, created_at) VALUES ($1, $2, $3, now()) RETURNING id

The confusing part: not every insert failed. About one in twenty-five succeeded, and agents reported that "retrying a few times sometimes works".

Reproducing it

CREATE TABLE t (id bigserial PRIMARY KEY, note text);
INSERT INTO t (id, note) VALUES (1, 'imported'), (2, 'imported'), (4, 'imported');
INSERT INTO t (note) VALUES ('new');  -- ERROR: Key (id)=(1) already exists.
INSERT INTO t (note) VALUES ('new');  -- ERROR: Key (id)=(2) already exists.
INSERT INTO t (note) VALUES ('new');  -- succeeds with id 3

What we ruled out

• Duplicate rows in the import. The source data had unique ids, and the primary key would have rejected duplicates during the import anyway.
• The application sending explicit ids. The failing INSERT statement did not include the id column; the value came from the column default.
• Replication lag or a split brain. All writes went to a single primary, and the failing key values were always low numbers, already present since the import.

Root cause

The id column was a bigserial, whose default is nextval('tickets_id_seq'). The import used COPY tickets (id, subject, ...) FROM STDIN with explicit ids, so the default never ran and the sequence was never advanced. It still stood at 1 while the table held ids up to 431,977. Each new insert drew the next sequence value and collided with an imported row, except where the old system had gaps from deleted tickets, which is why about 4% of inserts succeeded. Each failure also consumed a sequence value, so the problem would have "healed" itself after roughly 430,000 failed inserts.

SELECT last_value, is_called FROM tickets_id_seq;   -- 1188 | t  (by 09:00 on Monday)
SELECT max(id) FROM tickets;                         -- 431977

What is happening under the hood

A serial or bigserial column is shorthand for an integer column with a DEFAULT nextval(...) expression and an owned sequence. An identity column (GENERATED BY DEFAULT AS IDENTITY) uses an internal sequence the same way. In both cases the sequence is only consulted when the column is omitted from the INSERT. Supplying the value yourself, through INSERT or COPY, bypasses the sequence completely and PostgreSQL does not update it.

Sequences are also deliberately non-transactional. nextval() is never rolled back, even if the insert that used it fails. That avoids contention between concurrent inserts, but it means that failed inserts burn values, which is why the failure rate in our incident slowly changed over time and why gaps in ids are normal.

pg_dump and pg_restore handle this correctly because the dump includes a setval call for every sequence. The problem appears with ETL tools, custom COPY scripts, ORM seeders and logical replication, which copies rows but not sequence state.

Environment

Minimal reproduction

The SQL in the Investigate section is the minimal reproduction. The identity-column variant behaves the same way:

CREATE TABLE t2 (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, note text);
INSERT INTO t2 (id, note) VALUES (1, 'imported');
INSERT INTO t2 (note) VALUES ('new');  -- duplicate key on id 1

Who is affected

Anyone loading rows with explicit primary keys into a table whose key comes from a sequence: data migrations between databases, restoring single tables with COPY, seeding fixtures with fixed ids, merging tenants, and promoting a logical replication subscriber to primary.

The fix

We put the API in maintenance mode for two minutes and moved the sequence past the highest id:

BEGIN;
LOCK TABLE tickets IN EXCLUSIVE MODE;   -- blocks concurrent inserts, still allows reads
SELECT setval(pg_get_serial_sequence('tickets', 'id'),
              COALESCE(max(id), 0) + 1,
              false)
FROM tickets;
COMMIT;

pg_get_serial_sequence returns the sequence that belongs to a column, for both serial and identity columns, so we do not depend on naming conventions. The third argument false means the next nextval() returns exactly the value we set. The table lock avoids a race where another session inserts a higher explicit id between reading max(id) and calling setval.

Because the import touched 14 tables, we ran a generic version that resyncs every sequence-backed column in the schema:

DO $$
DECLARE
  r record;
  max_id bigint;
BEGIN
  FOR r IN
    SELECT n.nspname AS sch, c.relname AS tbl, a.attname AS col,
           pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS seq
    FROM pg_attribute a
    JOIN pg_class c     ON c.oid = a.attrelid AND c.relkind = 'r'
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public' AND a.attnum > 0 AND NOT a.attisdropped
  LOOP
    CONTINUE WHEN r.seq IS NULL;
    EXECUTE format('SELECT max(%I) FROM %I.%I', r.col, r.sch, r.tbl) INTO max_id;
    PERFORM setval(r.seq, COALESCE(max_id, 0) + 1, false);
    RAISE NOTICE '% -> %', r.seq, COALESCE(max_id, 0) + 1;
  END LOOP;
END $$;

Finally, the import tool now finishes with this step itself, so the problem cannot reappear on the next migration.

Alternatives and trade-offs

1. ALTER TABLE ... ALTER COLUMN id RESTART WITH n for identity columns. Equivalent to setval and more readable, but n must be a literal, so it needs a computed value from the script.
2. GENERATED ALWAYS AS IDENTITY. Rejects explicit ids unless the statement says OVERRIDING SYSTEM VALUE, which makes accidental bypasses visible in code review. Imports still need the resync afterwards.
3. Offsetting imported ids. Importing the acquired tickets as id + 10000000 would have avoided collisions without touching the sequence, but would have broken the ticket numbers customers already had. A separate external_ref column would be the cleaner design if numbers do not need to be preserved.
4. UUIDs or other client-generated keys. No sequence to drift, at the cost of larger keys and index locality. Worth considering for new systems that merge data often.

How to test the fix

The import tool's integration test now loads a fixture with explicit ids into a throwaway PostgreSQL container and then inserts a row without an id:

func TestImportLeavesSequencesUsable(t *testing.T) {
	ctx := context.Background()
	db := startPostgres(t) // testcontainers helper

	runImport(t, ctx, db, "testdata/tickets_sparse.csv") // ids 1..5000 with gaps

	var id int64
	err := db.QueryRow(ctx,
		`INSERT INTO tickets (subject, requester_id, status) VALUES ('t', 1, 'open') RETURNING id`,
	).Scan(&id)
	if err != nil {
		t.Fatalf("insert after import: %v", err)
	}
	if id <= 5000 {
		t.Fatalf("got id %d, want > 5000", id)
	}
}

go test ./internal/importer/... -run TestImportLeavesSequencesUsable -v

Confirming in production

After the resync, POST /tickets errors dropped to zero immediately and agents created around 3,100 tickets that day without a single 23505 error. We confirmed every sequence in the schema was ahead of its column with a drift check that we now run after every data load:

SELECT s.schemaname, s.sequencename, s.last_value
FROM   pg_sequences s
WHERE  s.schemaname = 'public'
ORDER  BY s.sequencename;

Combined with the max(id) of each owning table, the check reported 14 of 14 sequences ahead. The 4,800 failed requests during the morning were replayed from the API gateway logs where agents had not already retried them manually.

Regression guard

• The import tool runs the resync block as its final step and fails if any sequence is behind its column afterwards.
• A nightly job compares pg_sequences.last_value with max(id) for every sequence-owned column and alerts on any sequence that is behind.
• Our runbook for promoting a logical replication subscriber includes the same resync step.

Security implications

None beyond availability. Sequence values are not secret, but note that sequential ids exposed in URLs reveal volumes and allow enumeration; that was already true before the incident, and access checks on every ticket endpoint are what keep it safe. When writing a dynamic resync script like the one above, quote identifiers with format('%I') as shown, so that unusual table names cannot break out of the statement.

Prevention checklist

• After any load with explicit ids, resync sequences with setval and pg_get_serial_sequence.
• Prefer pg_dump and pg_restore for copies between PostgreSQL databases; they carry sequence state.
• Use GENERATED ALWAYS AS IDENTITY for new tables so explicit ids require an explicit override.
• Add a sequence drift check to migrations and to replication failover runbooks.
• Test imports end to end, including one insert that relies on the default id.

References

• PostgreSQL docs, Sequence Manipulation Functions (nextval, setval) — https://www.postgresql.org/docs/17/functions-sequence.html
• PostgreSQL docs, System Information Functions (pg_get_serial_sequence) — https://www.postgresql.org/docs/17/functions-info.html
• PostgreSQL docs, Serial Types — https://www.postgresql.org/docs/17/datatype-numeric.html#DATATYPE-SERIAL
• PostgreSQL docs, CREATE TABLE, identity columns — https://www.postgresql.org/docs/17/sql-createtable.html
• PostgreSQL docs, pg_sequences view — https://www.postgresql.org/docs/17/view-pg-sequences.html