How to Fix PostgreSQL Error: Duplicate Key Violates Unique Constraint

Pulse - Elasticsearch Operations Done Right

On this page

Impact Common Causes Troubleshooting and Resolution Steps Additional Information Frequently Asked Questions

The "Duplicate key value violates unique constraint" error occurs when attempting to insert or update a row with a value that already exists in a column or set of columns protected by a UNIQUE constraint or PRIMARY KEY. This is PostgreSQL's way of enforcing data integrity and preventing duplicate entries.

Impact

This error prevents INSERT and UPDATE operations from completing, potentially causing application errors, failed API requests, and user-facing issues. It can occur during bulk data imports, concurrent operations, or when business logic fails to check for existing records.

Common Causes

  1. Attempting to insert duplicate values in UNIQUE or PRIMARY KEY columns
  2. Concurrent insert operations on the same data
  3. Application logic not checking for existing records
  4. Sequence/serial column out of sync after manual data insertion
  5. Case-insensitive uniqueness requirements not enforced
  6. Bulk import containing duplicate data
  7. Race conditions in multi-threaded applications

Troubleshooting and Resolution Steps

  1. Identify the constraint and duplicate value:

    -- The error message shows the constraint name and value
-- Example error: duplicate key value violates unique constraint "users_email_key"
-- Detail: Key (email)=(user@example.com) already exists.

-- Find existing record
SELECT * FROM users WHERE email = 'user@example.com';

-- View constraint definition
\d users
-- Or
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

  2. Use INSERT ON CONFLICT (upsert):

    -- Do nothing if conflict occurs
INSERT INTO users (email, name)
VALUES ('user@example.com', 'John Doe')
ON CONFLICT (email) DO NOTHING;

-- Update on conflict
INSERT INTO users (email, name, updated_at)
VALUES ('user@example.com', 'John Doe', NOW())
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = EXCLUDED.updated_at;

-- Return whether inserted or updated
INSERT INTO users (email, name)
VALUES ('user@example.com', 'John Doe')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name
RETURNING id, (xmax = 0) AS inserted;

  3. Check for existing record before INSERT:

    -- Insert only if not exists
INSERT INTO users (email, name)
SELECT 'user@example.com', 'John Doe'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE email = 'user@example.com'
);

-- Using CTE
WITH new_user AS (
    SELECT 'user@example.com' AS email, 'John Doe' AS name
)
INSERT INTO users (email, name)
SELECT email, name FROM new_user
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE email = new_user.email
);

  4. Fix sequence after manual ID insertion:

    -- When manually inserting IDs, sequences can get out of sync
-- Check current sequence value
SELECT last_value FROM users_id_seq;

-- Find max ID in table
SELECT MAX(id) FROM users;

-- Reset sequence to max ID
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

-- Or set to specific value
SELECT setval('users_id_seq', 1000);

  5. Handle case-insensitive uniqueness:

    -- Create case-insensitive unique constraint
CREATE UNIQUE INDEX users_email_lower_idx ON users (LOWER(email));

-- Or use citext extension
CREATE EXTENSION IF NOT EXISTS citext;

ALTER TABLE users ALTER COLUMN email TYPE citext;
-- Now 'User@Example.com' and 'user@example.com' are treated as duplicates

  6. Find and remove duplicates:

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

-- Keep oldest, delete duplicates
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);

-- Or use window function
DELETE FROM users
WHERE id IN (
    SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
        FROM users
    ) t
    WHERE rn > 1
);

  7. Modify constraint if needed:

    -- Drop unique constraint
ALTER TABLE users DROP CONSTRAINT users_email_key;

-- Add unique constraint
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

-- Add composite unique constraint
ALTER TABLE users ADD CONSTRAINT users_email_tenant_key
    UNIQUE (email, tenant_id);

-- Create unique index with condition
CREATE UNIQUE INDEX users_email_active_idx
ON users (email)
WHERE deleted_at IS NULL;  -- Only enforce for non-deleted records

  8. Handle bulk insert with conflicts:

    -- Insert multiple rows, skip conflicts
INSERT INTO users (email, name)
VALUES
    ('user1@example.com', 'User 1'),
    ('user2@example.com', 'User 2'),
    ('user3@example.com', 'User 3')
ON CONFLICT (email) DO NOTHING;

-- Temporary table approach
CREATE TEMP TABLE temp_users (email VARCHAR, name VARCHAR);

COPY temp_users FROM '/path/to/file.csv' CSV;

INSERT INTO users (email, name)
SELECT t.email, t.name
FROM temp_users t
LEFT JOIN users u ON u.email = t.email
WHERE u.email IS NULL;

  9. Application-level handling:

    # Python with psycopg2
from psycopg2 import IntegrityError

try:
    cursor.execute(
        "INSERT INTO users (email, name) VALUES (%s, %s)",
        ('user@example.com', 'John Doe')
    )
    conn.commit()
except IntegrityError as e:
    conn.rollback()
    if 'unique constraint' in str(e):
        # Handle duplicate - maybe update instead
        cursor.execute(
            "UPDATE users SET name = %s WHERE email = %s",
            ('John Doe', 'user@example.com')
        )
        conn.commit()

  10. Prevent race conditions:

    -- Use SELECT FOR UPDATE to lock row
BEGIN;
SELECT * FROM users WHERE email = 'user@example.com' FOR UPDATE;
-- If exists, update; otherwise insert
COMMIT;

-- Advisory locks for application-level coordination
SELECT pg_advisory_lock(hashtext('user@example.com'));
-- Perform check and insert
SELECT pg_advisory_unlock(hashtext('user@example.com'));

Additional Information

  • Use ON CONFLICT clause (PostgreSQL 9.5+) for elegant upsert operations
  • Unique constraints can span multiple columns (composite keys)
  • NULL values don't violate unique constraints (multiple NULLs allowed)
  • Partial unique indexes allow conditional uniqueness
  • Consider using UUID primary keys to avoid sequence conflicts
  • Test concurrent scenarios during development

Frequently Asked Questions

Q: Can I have multiple NULL values in a UNIQUE column?
A: Yes, in PostgreSQL, NULL is not considered equal to NULL, so multiple NULL values are allowed in UNIQUE columns. Use UNIQUE NULLS NOT DISTINCT (PostgreSQL 15+) if you want to prevent multiple NULLs.

Q: What's the difference between UNIQUE constraint and UNIQUE index?
A: They're functionally equivalent. Creating a UNIQUE constraint automatically creates a UNIQUE index. The constraint provides more semantic meaning.

Q: How do I make a case-insensitive unique constraint?
A: Create a unique index on a lowercase version: CREATE UNIQUE INDEX ON users (LOWER(email)); or use the citext extension.

Q: Can I temporarily disable a unique constraint?
A: No, PostgreSQL doesn't support disabling constraints. You must drop and recreate it, which isn't recommended for unique constraints as it can lead to data integrity issues.

Q: How does ON CONFLICT work with composite unique keys?
A: Specify all columns: ON CONFLICT (email, tenant_id) DO UPDATE SET ...

Q: What's the performance impact of unique constraints?
A: Unique constraints use indexes, so there's minimal SELECT performance impact. INSERT/UPDATE operations have slight overhead for uniqueness checking, but this is generally negligible.

Pulse - Elasticsearch Operations Done Right

Pulse can solve your Elasticsearch issues

Subscribe to the Pulse Newsletter

Get early access to new Pulse features, insightful blogs & exclusive events , webinars, and workshops.

We use cookies to provide an optimized user experience and understand our traffic. To learn more, read our use of cookies; otherwise, please choose 'Accept Cookies' to continue using our website.