Fix: PostgreSQL 'duplicate key violates unique constraint' Error

Problem

Your application tries to insert a row and PostgreSQL throws:

ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=([email protected]) already exists.

Symptoms

  • Insert fails intermittently, especially under concurrent load
  • The same INSERT works when retried manually
  • Occurs in API endpoints receiving duplicate requests
  • Race conditions between multiple processes

Root Cause

PostgreSQL enforces uniqueness via unique indexes. When a column (or combination of columns) has a unique constraint, no two rows can share the same value. This error occurs when:

  1. A row with that value already exists — straightforward duplicate insert
  2. A race condition — two concurrent transactions both try to insert the same value; the first succeeds, the second fails
  3. Sequence gap — the auto-increment sequence for a SERIAL column is behind, trying to insert an ID that already exists (less common)

Solution

Option 1: Use INSERT ... ON CONFLICT (UPSERT)

When you want to insert-or-update in a single atomic statement:

INSERT INTO users (email, name, login_count)
VALUES ('[email protected]', 'John Doe', 1)
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  login_count = users.login_count + 1;
  • ON CONFLICT (email) specifies the conflicting column/constraint
  • DO UPDATE merges the new data into the existing row
  • DO NOTHING silently skips the insert if a conflict exists
  • EXCLUDED refers to the values that were proposed for insertion

Option 2: Check before insert (with caution)

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM users WHERE email = '[email protected]') THEN
    INSERT INTO users (email, name) VALUES ('[email protected]', 'John Doe');
  END IF;
END $$;

Caveat: This is not safe under concurrency. Two transactions can both see no row exists and both try to insert — one will still fail.

Option 3: Fix sequence issues

If you’re importing data and the sequence is behind:

-- Check the current sequence value
SELECT last_value FROM users_id_seq;

-- Check the max actual value
SELECT MAX(id) FROM users;

-- Reset the sequence
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

Option 4: Use transactions for multi-step operations

BEGIN;
  -- Delete the old, insert the new
  DELETE FROM users WHERE email = '[email protected]';
  INSERT INTO users (email, name) VALUES ('[email protected]', 'John Smith');
COMMIT;

Within a transaction, the DELETE + INSERT are atomic — no other transaction sees the intermediate state.

Understanding Race Conditions

Consider two concurrent requests:

Time  Transaction A              Transaction B
------------------------------------------------------
T1    Check: no row for email X
T2                              Check: no row for email X
T3    INSERT email X → OK
T4                              INSERT email X → ERROR!

Both saw an empty table and both tried to insert. ON CONFLICT solves this because the check-and-insert is a single atomic operation at the database level — no race condition possible.

Verification

-- Test ON CONFLICT behaviour
INSERT INTO users (email, name) VALUES ('[email protected]', 'Test');
-- First insert succeeds

INSERT INTO users (email, name) VALUES ('[email protected]', 'Test Updated')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
-- Second insert updates the row instead of failing

SELECT * FROM users WHERE email = '[email protected]';
-- Shows name = 'Test Updated'

Prevention

  • Use ON CONFLICT for any insert that might encounter duplicates in production
  • Add unique constraints at the database level, not just application-level checks
  • Don’t rely on application-level uniqueness checks — they’re not atomic
  • Use SERIAL or IDENTITY columns with auto-increment to avoid manually managing IDs
  • Log and alert on unexpected constraint violations — they may indicate a bug

References

Advertisement