Fix PostgreSQL 'duplicate key value violates unique constraint' Error

What's happening

You insert or update a row and PostgreSQL throws:

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

Read the DETAIL line first — it tells you exactly which column (id) and which value (42) caused the conflict. That's 90% of your debugging done. The remaining question is why that value already exists.

Common causes

A SERIAL or BIGSERIAL sequence is out of sync with the actual table data — very common after bulk imports or pg_restore
You're manually specifying an ID that already exists in the table
A unique column (email, username, slug) received a duplicate value
Two concurrent requests raced to insert the same row

Cause 1: Sequence out of sync (most common)

Picture this: you just ran pg_restore on a database with 50,000 users. Those rows came in with explicit IDs, so PostgreSQL's sequence never incremented. It still thinks the next ID is 1 — but your table already has rows up to 50000. Every new insert crashes immediately.

Check the sequence value vs actual max ID

-- What will the sequence generate next?
SELECT last_value FROM users_id_seq;

-- What's the highest ID currently in the table?
SELECT MAX(id) FROM users;

If last_value is lower than MAX(id), that's your problem right there.

Fix: reset the sequence

-- Reset to max(id) so the next insert gets max(id) + 1
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

Prefer not to hardcode the sequence name? Use pg_get_serial_sequence instead:

SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  (SELECT MAX(id) FROM users)
);

The next INSERT that omits id will now get MAX(id) + 1 — clean and safe.

Fix all out-of-sync sequences at once

After restoring an entire database, dozens of tables can be affected simultaneously. Run this script once to reset every primary key sequence in one shot:

DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT
      tc.table_name,
      kc.column_name,
      pg_get_serial_sequence(tc.table_name, kc.column_name) AS seq
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kc
      ON tc.constraint_name = kc.constraint_name
    WHERE tc.constraint_type = 'PRIMARY KEY'
      AND pg_get_serial_sequence(tc.table_name, kc.column_name) IS NOT NULL
  LOOP
    EXECUTE format(
      'SELECT setval(%L, COALESCE(MAX(%I), 1)) FROM %I',
      r.seq, r.column_name, r.table_name
    );
  END LOOP;
END;
$$;

Cause 2: Duplicate value in a unique column

The constraint isn't always on the primary key. This one targets the email column:

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

Find the conflicting row

SELECT * FROM users WHERE email = 'user@example.com';

Option A: Skip duplicates silently

INSERT INTO users (email, name)
VALUES ('user@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

Option B: Upsert — update the existing row instead

INSERT INTO users (email, name, updated_at)
VALUES ('user@example.com', 'Alice Updated', NOW())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

EXCLUDED is a virtual table holding the row that failed to insert. Reference it to pull in the new values you wanted to write.

Cause 3: Explicit ID conflicts during bulk insert

Migrating data from another system? If the source rows carry their own IDs and some of those IDs already exist in your target table, every conflicting row will fail.

Spot conflicts before inserting

-- See which incoming IDs already exist
SELECT s.id
FROM staging_users s
INNER JOIN users u ON s.id = u.id;

Insert only the non-conflicting rows

INSERT INTO users (id, email, name)
SELECT id, email, name FROM staging_users
ON CONFLICT (id) DO NOTHING;

Cause 4: Race condition on concurrent inserts

Two API requests arrive within milliseconds of each other. Both check the database, both see no existing row, and both fire an insert. One wins. The other hits the constraint.

Application-level checks can't prevent this — the gap between check and insert is enough for a second request to slip through. Handle it at the database level instead:

-- Python (psycopg2) — catch the error and treat it as "already exists"
try:
    cursor.execute(
        "INSERT INTO users (email) VALUES (%s)",
        (email,)
    )
    conn.commit()
except psycopg2.errors.UniqueViolation:
    conn.rollback()
    cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

Better yet, push the conflict handling into SQL itself so the insert is idempotent from the start:

INSERT INTO users (email)
VALUES ('user@example.com')
ON CONFLICT (email) DO NOTHING
RETURNING id;

Verify the fix worked

-- Confirm the sequence is now ahead of the current max
SELECT
  last_value AS sequence_next,
  (SELECT MAX(id) FROM users) AS table_max
FROM users_id_seq;

-- Test a plain insert — no id specified, should succeed
INSERT INTO users (email, name) VALUES ('test@example.com', 'Test');
SELECT * FROM users WHERE email = 'test@example.com';

What to remember

After pg_restore or any bulk import with explicit IDs — reset sequences before your application goes live. Add it to your restore runbook so it's never skipped.
ON CONFLICT beats application-level duplicate checks every time. It runs atomically inside the database; your app code can't guarantee that.
The DETAIL line is your fastest debug tool. It names the constraint and the conflicting value — start there, not with the stack trace.
Consider switching to GENERATED ALWAYS AS IDENTITY (PostgreSQL 10+) over SERIAL. PostgreSQL manages identity columns more strictly, making sequence drift much harder to trigger accidentally.