duplicate key value violates unique constraint

The exact error string

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

The constraint name (users_email_key) and the DETAIL tell you exactly what collided: column email, value ada@example.com. Postgres enforces uniqueness, so it rejects the duplicate row.

Fix 1: upsert with ON CONFLICT

If a duplicate is expected (re-running an import, "create or update"), tell Postgres what to do on conflict instead of letting it raise:

-- skip duplicates silently
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada')
ON CONFLICT (email) DO NOTHING;

-- or update the existing row (true upsert)
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

EXCLUDED refers to the row you tried to insert. This is atomic and race-safe — unlike a check-then-insert, which can still collide between the check and the insert.

Fix 2: handle the violation in code

When a duplicate is a real client error (e.g. signing up with an existing email), catch SQLSTATE 23505 and return a clean response instead of a 500:

# Python / psycopg
import psycopg2
try:
    cur.execute("INSERT INTO users (email) VALUES (%s)", (email,))
except psycopg2.errors.UniqueViolation:
    conn.rollback()
    return "Email already registered", 409   # Conflict

// node-postgres
try { await pool.query('INSERT INTO users(email) VALUES($1)', [email]); }
catch (e) { if (e.code === '23505') return res.status(409).send('exists'); throw e; }

Fix 3: the id sequence is out of sync

A very common variant: after a data restore or bulk import that set explicit id values, the SERIAL/IDENTITY sequence still points at a low number, so the next auto-generated id collides. Reset the sequence to the current max:

-- move the sequence past the highest existing id
SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  (SELECT COALESCE(MAX(id), 0) FROM users)
);

-- next INSERT now gets max(id)+1 and no longer collides

Fix 4: find which constraint and columns

For a composite unique constraint, the conflict may span several columns. Inspect the table to see exactly what must be unique:

-- in psql: list constraints and indexes on the table
\d users

-- the unique constraint shows its column list, e.g.
-- "users_org_id_email_key" UNIQUE CONSTRAINT, (org_id, email)

Debugging checklist

• ✓ Read the DETAIL — it names the column and the duplicate value
• ✓ Expected duplicate? Use ON CONFLICT DO NOTHING / DO UPDATE (upsert)
• ✓ Real client error? Catch SQLSTATE 23505 and return 409 Conflict
• ✓ Collision on id after an import? Reset the sequence with setval
• ✓ Don't check-then-insert — it has a race; ON CONFLICT is atomic
• ✓ Composite constraint? \d table shows which columns must be unique together