How do I fix "duplicate key value violates unique constraint" on the primary key in PostgreSQL?

pg_get_serial_sequence works for both serial and identity columns, so you don't need to guess the sequence's name. The COALESCE handles an empty table.

For identity columns there's also:

ALTER TABLE users ALTER COLUMN id RESTART WITH 50001;

Fix every table at once

After a big migration, reset all sequences owned by table columns:

DO $$
DECLARE r record;
BEGIN
  FOR r IN
    SELECT n.nspname AS schema_name, c.relname AS table_name, a.attname AS column_name,
           pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS seq
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
    WHERE c.relkind = 'r'
      AND n.nspname NOT IN ('pg_catalog', 'information_schema')
      AND pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) IS NOT NULL
  LOOP
    EXECUTE format(
      'SELECT setval(%L, COALESCE((SELECT max(%I) FROM %I.%I), 0) + 1, false)',
      r.seq, r.column_name, r.schema_name, r.table_name);
  END LOOP;
END $$;

Run it as part of every data-migration script, so it can't be forgotten.

Prevent it

• Use GENERATED ALWAYS AS IDENTITY for new tables. With ALWAYS, an insert that supplies an explicit id is rejected unless it says OVERRIDING SYSTEM VALUE, which makes accidental manual ids visible:

  CREATE TABLE users (
      id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      email text NOT NULL UNIQUE
  );
  

• When loading data with ids on purpose, finish with a setval like the one above.

• Don't reset a sequence to a lower value "to fill the gaps". Gaps are normal (rolled-back transactions and cached values consume numbers). Ids aren't meant to be contiguous.

Related causes of the same error

If the sequence is fine and you still get duplicate-key errors, the conflict is real: two sessions inserting the same natural key, or a retried request inserting twice. That's a job for INSERT … ON CONFLICT and idempotency keys, not for sequences.

Checklist

1. Duplicate primary key on a default insert means the sequence is behind max(id).
2. Fix it with setval(pg_get_serial_sequence(...), max(id) + 1, false).
3. Reset every sequence after migrations or imports with explicit ids.
4. Prefer GENERATED ALWAYS AS IDENTITY, and never try to close id gaps.

Should I use timestamp or timestamptz in PostgreSQL?

timestamptz doesn't store a time zone, and timestamp silently drops the offset you pass in. What each type really stores, where timestamp causes bugs and the one case where it's right.

#data-types#schema-design