ERROR: duplicate key value violates unique constraint

The PostgreSQL error “duplicate key value violates unique constraint” means an INSERT or UPDATE attempted to create a duplicate value in a column or set of columns that has a unique constraint or unique index.

What It Means

PostgreSQL enforces uniqueness through unique constraints, primary keys, and unique indexes. When a new row would cause a duplicate in one of these, the database aborts the operation and returns this error, including the constraint name and the offending value.

Why It Happens

• You inserted a row with a primary key value that already exists.
• Application logic did not check for existing records before inserting.
• A unique constraint exists on an email, username, or SKU column.
• A sequence (e.g., id from a SERIAL column) got out of sync — common after manual inserts without updating the sequence.
• A bulk import or migration loaded data that contains duplicates.
• An upsert operation used the wrong conflict target.

How to Fix It

1. Find the duplicate row

SELECT id, email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;

2. Delete or merge the duplicate

DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) sub WHERE rn > 1
);

3. Use ON CONFLICT (upsert)

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

4. Fix a sequence that is out of sync

SELECT setval('users_id_seq', COALESCE(MAX(id), 0) + 1, false) FROM users;

5. Disable the trigger temporarily (advanced)

ALTER TABLE users DISABLE TRIGGER ALL;
-- perform your import
ALTER TABLE users ENABLE TRIGGER ALL;

FAQ