Blog
3 min read

"duplicate key value violates unique constraint": Causes and Fixes

Postgres refused an insert because a value that must be unique already exists. The two very different cases — a real duplicate (like an email that's taken) and an out-of-sync ID sequence after importing data — and how to fix each, including ON CONFLICT and resetting sequences.

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

A unique constraint says no two rows may have the same value in a column (or combination of columns). Your insert or update would have created a duplicate, so Postgres refused.

The DETAIL line tells you which column and value. There are two very different situations behind this error.

Situation 1: a genuine duplicate

users_email_key with an email in the detail — someone tried to sign up with an email that's already registered. The constraint is doing its job.

Handle it gracefully

Don't show users a database error. Catch it and respond properly:

try {
  await db.insert(users).values({ email })
} catch (err) {
  if (err.code === '23505') {          // unique_violation
    return res.status(409).json({ error: 'An account with this email already exists.' })
  }
  throw err
}

23505 is Postgres's error code for unique violations; most drivers and ORMs expose it (Prisma reports it as P2002).

Or use ON CONFLICT

If a duplicate should update or be ignored:

INSERT INTO subscribers (email) VALUES ('ada@example.com')
ON CONFLICT (email) DO NOTHING;

INSERT INTO settings (user_id, theme) VALUES (42, 'dark')
ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme;

This is an upsert. (Postgres upsert)

Double submissions

If the duplicate comes from a user double-clicking "Submit" or a retried request, disable the button while submitting and consider idempotency keys. (Idempotency keys)

Case and whitespace

Ada@Example.com and ada@example.com are different strings. Normalise emails (lowercase, trimmed) before saving, or use a case-insensitive unique index: CREATE UNIQUE INDEX ON users (lower(email));

Situation 2: the ID sequence is out of sync

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

This one is about the primary key (_pkey), and the value is a small number. You're not supplying an ID; Postgres is generating one — and generating one that's already used.

Why: auto-increment IDs come from a sequence that hands out the next number. If rows were inserted with explicit IDs — from a CSV import, a restored dump, seed data, or copying data between databases — the sequence doesn't know. It's still at 7 while the table already has IDs up to 5,000. (Import a CSV into Postgres, Database seeding)

Fix: move the sequence past the highest ID

SELECT setval(
  pg_get_serial_sequence('orders', 'id'),
  (SELECT COALESCE(MAX(id), 0) FROM orders) + 1,
  false
);

Run it for each affected table. New inserts then get fresh IDs.

Prevent it

  • When seeding or importing, let the database generate IDs (leave the id column out), or reset sequences afterwards.
  • Use GENERATED ALWAYS AS IDENTITY columns, which reject explicit IDs unless you deliberately override. (UUID vs auto-increment)

Situation 3: composite keys and join tables

Key (post_id, tag_id)=(12, 3) already exists.

You tried to add the same tag to the same post twice. Use ON CONFLICT DO NOTHING, or check first. (Database relationships)

Finding the constraint

The constraint name tells you the table and column (users_email_key = unique on users.email). See all constraints on a table in psql:

\d users

EasySpawn gives your app its own Postgres database and a terminal with Claude Code, which can read the error, check the sequence and fix it for you. See how it works or join the waitlist.

Related: Postgres Upsert (ON CONFLICT) · UUID vs Auto-Increment IDs · Primary Key vs Foreign Key · Import a CSV Into PostgreSQL

Keep reading