🚨 Postgres 'duplicate key value violates unique constraint': Causes and Fix

Overview

PostgreSQL raises this error when an INSERT or UPDATE would put a value into a column (or set of columns) that a unique constraint or unique index says must not repeat. The write is rejected and the transaction is aborted.

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

The DETAIL line is the important part: it names the exact column(s) and the exact value that collided. users_email_key is the auto-generated name for the UNIQUE constraint on users.email. The value a@b.com already exists in another row.

Symptoms

• Inserts fail for specific values while others succeed.
• After a data load or restore, every insert on a serial primary key fails as duplicate.
• Under concurrency, occasional failures on a column that “should” be unique.
• The DETAIL line always names a constraint and the offending key value.

INSERT INTO users (email) VALUES ('a@b.com');
ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(a@b.com) already exists.

Common Root Causes

1. A genuine duplicate insert

The most literal case: the value truly already exists. The application tried to create a second users row with an email that is already taken.

SELECT id, email FROM users WHERE email = 'a@b.com';

2. Race condition between check-then-insert

Code that does SELECT ... WHERE email = ? and then INSERT if not found is not atomic. Two concurrent requests both see “not found” and both insert — one wins, the other hits the constraint. The constraint is doing its job; the app logic is racy.

3. Sequence / serial out of sync

After a manual insert with an explicit id, or a restore that copied rows without advancing the sequence, the SERIAL/IDENTITY sequence still returns values that already exist as primary keys.

SELECT max(id) AS max_id,
       (SELECT last_value FROM orders_id_seq) AS seq_last
FROM orders;

 max_id | seq_last
--------+----------
   5012 |      4001
(1 row)

The sequence is behind the data — the next insert will reuse an existing id and fail.

4. Retries without idempotency

A client that retries a request after a timeout (but the first attempt actually committed) sends the same insert twice. Without an idempotency key or upsert, the second attempt collides.

5. Case or normalization collisions

A@B.com and a@b.com are different strings to a plain unique index but the same address to your users. If the app lowercases inconsistently, “new” values collide with normalized existing ones.

How to diagnose

Step 1: Inspect the constraint that fired

Look at the table definition to see exactly which columns the named constraint covers:

appdb=# \d users
                              Table "public.users"
 Column |  Type   | Collation | Nullable |           Default
--------+---------+-----------+----------+------------------------------
 id     | integer |           | not null | nextval('users_id_seq'::...)
 email  | text    |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)

Now you know users_email_key enforces uniqueness on email alone.

Step 2: Find the offending rows

If you suspect existing duplicates (e.g., before adding a constraint), list them:

SELECT email, count(*) AS n, array_agg(id ORDER BY id) AS ids
FROM users
GROUP BY email
HAVING count(*) > 1
ORDER BY n DESC;

   email   | n |    ids
-----------+---+-----------
 a@b.com   | 2 | {17,4820}
(1 row)

Step 3: Check the sequence against the data

When the failing key is a serial/identity column, compare the sequence’s next value to the current maximum:

SELECT pg_get_serial_sequence('public.orders', 'id') AS seq,
       (SELECT max(id) FROM orders)                  AS max_id,
       nextval(pg_get_serial_sequence('public.orders', 'id')) AS next_val;

If next_val is at or below max_id, the sequence is stale and must be reseeded.

Fixes

Use an upsert (ON CONFLICT)

When “insert or ignore” or “insert or update” is the intended behavior, let Postgres resolve the collision atomically instead of failing:

-- Ignore the duplicate:
INSERT INTO users (email)
VALUES ('a@b.com')
ON CONFLICT (email) DO NOTHING;

-- Or update the existing row:
INSERT INTO customers (email, name)
VALUES ('a@b.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

This also closes the check-then-insert race, because the conflict is handled inside a single atomic statement.

Reseed the sequence

Advance the sequence past the current maximum so it stops handing out used ids:

SELECT setval(
  pg_get_serial_sequence('public.orders', 'id'),
  (SELECT max(id) FROM orders)
);

setval to max(id) makes the next nextval return max(id) + 1.

Dedupe existing rows

Before you can add or trust a unique constraint, remove the duplicates, keeping the lowest id:

DELETE FROM users a
USING users b
WHERE a.email = b.email
  AND a.id > b.id;

Verify with the HAVING count(*) > 1 query from Step 2 before and after.

Enforce it with a unique index

If the collision should be prevented going forward, add the constraint (after deduping). A partial or expression index can also normalize case:

-- Case-insensitive uniqueness on email:
CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email));

Then have the application write normalized values so retries and case variants map to the same key.

What to watch out for

• The DETAIL line names the exact column(s) and value — read it before guessing which constraint fired.
• ON CONFLICT needs a matching unique constraint or index on the specified columns, or it errors with “no unique or exclusion constraint matching”.
• setval(seq, max(id)) sets the last value; the next insert gets max(id)+1. Do not off-by-one it.
• Reseeding only fixes the sequence; if duplicates already landed in the data, you must dedupe separately.
• A plain unique index is case- and whitespace-sensitive; use lower()/trim() expression indexes if your notion of “same” is looser than exact bytes.

Related

• Postgres Error: ‘null value violates not-null constraint’
• Postgres Error: ‘relation does not exist’
• Postgres Error: ‘there is no unique constraint matching given keys’