duplicate key value violates unique constraint

This error occurs when you try to insert or update a row with a value that already exists in a column with a UNIQUE constraint.

The duplicate key value violates unique constraint error occurs when inserting or updating data that would create a duplicate in a unique column.

Understanding the Error

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

PostgreSQL enforces uniqueness and rejects any operation that would create duplicates.

Common Causes

1. Inserting Duplicate Data

SQL

INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice 2');  -- Error!

2. Primary Key Collision

SQL

INSERT INTO products (id, name) VALUES (1, 'Widget');
INSERT INTO products (id, name) VALUES (1, 'Gadget');  -- Error: id 1 exists

3. Bulk Import Conflicts

Importing data that contains duplicates or conflicts with existing records.

How to Fix It

Solution 1: Use ON CONFLICT (Upsert)

SQL

-- Insert or update on conflict
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice Updated')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Solution 2: Use ON CONFLICT DO NOTHING

SQL

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

Solution 3: Check Before Insert

SQL

INSERT INTO users (email, name)
SELECT 'alice@example.com', 'Alice'
WHERE NOT EXISTS (
  SELECT 1 FROM users WHERE email = 'alice@example.com'
);

Solution 4: Use Transactions with Exception Handling

SQL

DO $$
BEGIN
  INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'User already exists';
END $$;

Solution 5: Let PostgreSQL Generate IDs

SQL

-- Use SERIAL or IDENTITY for auto-generated primary keys
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(100)
);

-- Don't specify id, let PostgreSQL handle it
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');

Best Practices

Use ON CONFLICT for upsert operations
Use SERIAL/IDENTITY for auto-generated primary keys
Add UNIQUE constraints during table design, not after
Validate data before bulk imports
Handle exceptions in application code