How to Fix Duplicate Key Violation in PostgreSQL

A duplicate key violation in PostgreSQL happens when you try to insert a row with a value that already exists in a column defined as UNIQUE or PRIMARY KEY. To fix it, ensure the inserted values are unique or use ON CONFLICT clauses to handle duplicates gracefully.

Why This Happens

This error occurs because PostgreSQL enforces uniqueness on columns marked as PRIMARY KEY or UNIQUE. When you try to insert a row with a value that already exists in these columns, PostgreSQL stops the operation to keep data consistent.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE
);

INSERT INTO users (email) VALUES ('[e-mail]');
INSERT INTO users (email) VALUES ('[e-mail]');

Output

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

The Fix

To fix this, you can either insert only unique values or use PostgreSQL's ON CONFLICT clause to skip or update duplicates. This lets you handle duplicates without errors.

sql

INSERT INTO users (email) VALUES ('[e-mail]')
ON CONFLICT (email) DO NOTHING;

Output

INSERT 0 0

Prevention

Always check your data before inserting to avoid duplicates. Use ON CONFLICT for safe inserts. Regularly clean your data and consider adding application-level checks to prevent duplicate entries.

Related Errors

Other common errors include foreign key violations when referenced data is missing, and not-null violations when required fields are empty. Handling constraints carefully helps avoid these.

Key Takeaways

Duplicate key violations happen when inserting repeated values in unique or primary key columns.

Use ON CONFLICT clauses to handle duplicates without errors.

Validate data before insert to prevent duplicates.

Regularly clean and audit your database for unique constraint compliance.

Understand related constraint errors to maintain data integrity.