How to Fix "ERROR: Duplicate Key Value Violates Unique Constraint"

An INSERT or UPDATE fails because it would create a duplicate value in a column that's supposed to be unique. PostgreSQL is doing exactly what a unique constraint is designed to do — the fix depends on whether this is expected behavior your application needs to handle gracefully, or a sign of a genuine data or sequence problem.

The Problem

A normal-looking insert fails, naming the specific constraint that blocked it:

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

It also commonly shows up on primary key columns, especially after a bulk data import or a sequence getting out of sync:

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

Why It Happens

This error is PostgreSQL's unique constraint mechanism working correctly — the real question is why your application produced a value that already exists. Common causes:

• Genuinely duplicate data — a user submitted a form twice, or two processes are trying to create the same logical record concurrently.
• A race condition — application code checks whether a value exists (SELECT ... WHERE email = ?) and then inserts if not found, but between the check and the insert, another concurrent request inserted the same value first.
• A sequence out of sync with actual table data — commonly after a bulk import or data restore that explicitly set primary key values without also updating the auto-increment sequence, causing the sequence's next generated value to collide with an already-existing row.
• A unique index intended to be case-insensitive or otherwise normalized isn't actually behaving that way, allowing near-duplicates like Alice@example.com and alice@example.com to both pass, but a byte-identical duplicate still correctly fails.

The Fix

For genuinely expected duplicate submissions (a common, valid scenario like a "sign up" form being double-clicked), handle the conflict gracefully at the database level using ON CONFLICT instead of relying on a pre-check and separate insert:

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

Or update the existing row instead of silently ignoring the conflict, if that's the more appropriate behavior:

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

This single atomic statement eliminates the race condition entirely, since the check-and-insert now happens as one indivisible database operation rather than two separate steps with a window for another process to interfere in between.

If the problem is a sequence out of sync after a bulk import, check the current sequence value against the actual maximum ID in the table:

SELECT last_value FROM users_id_seq;
SELECT MAX(id) FROM users;

If the sequence is behind the actual data (common after restoring a dump that inserted explicit IDs), resync it to continue from the correct point:

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

Verify the fix by checking that new inserts now generate IDs beyond the existing maximum:

INSERT INTO users (name) VALUES ('Bob') RETURNING id;

Still Not Working?

If you're confident the value shouldn't already exist but the error persists, check for soft-deleted or archived rows still occupying the unique value — a common pattern where a "deleted" flag is set instead of actually removing the row, but the unique constraint still applies to the underlying (still-present) data regardless of that flag:

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

If a soft-deleted row is the culprit, either adjust the unique constraint to only apply to non-deleted rows using a partial index, or handle reactivation explicitly in your application logic instead of trying to insert a fresh duplicate:

-- Partial unique index: only enforce uniqueness among active (non-deleted) rows
CREATE UNIQUE INDEX users_email_active_idx ON users (email) WHERE deleted_at IS NULL;

This allows a deleted row to keep its old email value on record for history purposes while still permitting a new active row with the same email, which is often exactly the behavior a soft-delete pattern is supposed to provide but silently breaks without this kind of partial index.