📋

Description

This error indicates an attempt to insert or update data that would violate a unique constraint on a table. It occurs when a value for a column (or set of columns) that is defined as unique already exists in the table, preventing data duplication.

💬

Error Message

unique violation

🔍

Known Causes

4 known causes

⚠️

Duplicate Key Insertion

Attempting to insert a new row where a value in a unique column already exists in an existing row.

⚠️

Updating to an Existing Value

Modifying an existing row's unique column to a value that is already present in another row in the same table.

⚠️

Concurrent Transactions

Two or more transactions simultaneously attempt to insert or update the same unique key, with one succeeding and the others failing.

⚠️

Data Migration Issues

Importing data that contains duplicate values for unique columns into a table with an active unique constraint.

🛠️

Solutions

5 solutions available

1. Use ON CONFLICT DO NOTHING easy

Skip insert if duplicate exists

1

Add ON CONFLICT clause to INSERT

INSERT INTO users (email, name) VALUES ('test@example.com', 'John')
ON CONFLICT DO NOTHING;

2

Specify conflict column

INSERT INTO users (email, name) VALUES ('test@example.com', 'John')
ON CONFLICT (email) DO NOTHING;

2. Use ON CONFLICT DO UPDATE (Upsert) easy

Update existing record on conflict

1

Upsert - update if exists, insert if not

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

2

Update multiple columns

INSERT INTO users (email, name, updated_at) 
VALUES ('test@example.com', 'John', NOW())
ON CONFLICT (email) DO UPDATE SET 
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

3. Check Before Insert easy

Verify record doesn't exist first

1

Use WHERE NOT EXISTS

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

4. Find and Remove Duplicates medium

Clean up existing duplicate data

1

Find duplicates

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

2

Delete duplicates keeping lowest id

DELETE FROM users a USING users b
WHERE a.id > b.id AND a.email = b.email;

3

Or using window function

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

5. Handle in Application Code medium

Catch and handle the constraint violation

1

Python/psycopg2

from psycopg2 import errors

try:
    cursor.execute("INSERT INTO users (email) VALUES (%s)", [email])
    conn.commit()
except errors.UniqueViolation:
    conn.rollback()
    # Handle duplicate - update or skip

2

Node.js/pg

try {
  await client.query('INSERT INTO users (email) VALUES ($1)', [email]);
} catch (err) {
  if (err.code === '23505') {
    // Handle duplicate
    console.log('Email already exists');
  } else {
    throw err;
  }
}

🔗

Related Errors

5 related errors

PostgreSQL Error 23505: Unique Constraint Violation