ERROR: duplicate key value violates unique constraint

• [email protected]
• 19 Sep, 2025
• 0 Comments
• 1 Min Read

ERROR: duplicate key value violates unique constraint

PostgreSQL Duplicate Key Value Violates Unique Constraint – Error Explained

If you are inserting or updating data in PostgreSQL and encounter:

ERROR: duplicate key value violates unique constraint "table_column_key"

Why Does This Error Happen?

The error usually occurs because:

• You are inserting duplicate values in a column with a UNIQUE constraint.

• The same primary key is being used for multiple rows.

• Data import or migration has repeated values.

• Application logic is not handling duplicates properly.

5 Effective Fixes for PostgreSQL Duplicate Key Value Violates Unique Constraint Error

1. Check which constraint is violated

\d table_name

Look for UNIQUE or PRIMARY KEY constraints on the column.

2. Remove duplicates before insert

INSERT INTO table_name (id, name)
SELECT DISTINCT id, name FROM temp_table;

3. Use ON CONFLICT (UPSERT)

Instead of failing, PostgreSQL can update existing rows:

INSERT INTO table_name (id, name) 
VALUES (1, 'John') 
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

4. Delete duplicate rows

DELETE FROM table_name a
USING table_name b
WHERE a.ctid < b.ctid
AND a.id = b.id;

5. Redesign constraints if needed

If duplicates are acceptable, remove or adjust the constraint:

ALTER TABLE table_name DROP CONSTRAINT table_column_key;