Fix: PostgreSQL ERROR: duplicate key value violates unique constraint

The Error

You try to insert a row into a PostgreSQL table and get:

ERROR: duplicate key value violates unique constraint "users_pkey"
DETAIL: Key (id)=(42) already exists.

Or one of these variations:

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

django.db.utils.IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey"
DETAIL: Key (id)=(7) already exists.

ERROR: duplicate key value violates unique constraint "unique_order_number"
DETAIL: Key (order_number)=(ORD-1001) already exists.

All of these mean the same thing: PostgreSQL tried to insert (or update) a row, but the value being written already exists in a column (or combination of columns) that has a unique constraint or unique index.

Why This Happens

PostgreSQL enforces uniqueness through constraints and indexes. When you define a column as PRIMARY KEY, UNIQUE, or create a UNIQUE INDEX, PostgreSQL guarantees that no two rows can have the same value in that column (or set of columns).

The most common scenario is a desynchronized sequence. PostgreSQL uses sequences to auto-generate values for SERIAL, BIGSERIAL, and IDENTITY columns. The sequence tracks the next value to assign. If data was inserted manually, restored from a backup, or imported from a CSV without advancing the sequence, the sequence falls behind the actual data. The next time PostgreSQL tries to auto-generate an ID, it picks a value that already exists in the table.

Other causes include:

• Duplicate data in the source. You’re inserting rows with values that genuinely conflict with existing data.
• Missing ON CONFLICT handling. Your insert statement doesn’t account for the possibility that a matching row already exists. This is particularly common when syncing data from external systems.
• Concurrent inserts. Two transactions try to insert the same unique value at the same time, and one of them fails. This is a common issue in high-traffic applications — similar to the connection issues described in Fix: PostgreSQL Connection Refused.
• Bulk imports or migrations. Tools like pg_restore, COPY, or Django’s loaddata insert rows with explicit IDs without updating the underlying sequence.
• ORM-level caching. Your application framework caches or pre-assigns IDs that collide with database state.

Fix 1: Reset the Sequence with setval and pg_get_serial_sequence

This is the most common fix. The sequence that generates auto-increment IDs has fallen behind the actual maximum ID in the table. You need to reset it.

First, find the sequence name:

SELECT pg_get_serial_sequence('users', 'id');

This returns something like public.users_id_seq. Now reset it to the current maximum value:

SELECT setval(pg_get_serial_sequence('users', 'id'), COALESCE(MAX(id), 1)) FROM users;

The COALESCE(MAX(id), 1) handles the edge case where the table is empty (MAX returns NULL).

If you want the next insert to use MAX(id) + 1 (the default behavior), this is all you need. If you want to explicitly control whether the current value has been “used,” pass a third argument:

-- Next call to nextval() will return MAX(id) + 1
SELECT setval(pg_get_serial_sequence('users', 'id'), COALESCE(MAX(id), 1), true) FROM users;

-- Next call to nextval() will return MAX(id) (i.e., the value itself is not yet "used")
SELECT setval(pg_get_serial_sequence('users', 'id'), COALESCE(MAX(id), 1), false) FROM users;

In almost all cases, the true variant (or omitting the third argument, which defaults to true) is what you want.

Fix 2: Find the Max ID and Reset Manually

If pg_get_serial_sequence returns NULL (which happens when the column isn’t a true SERIAL column, or the sequence ownership isn’t set), you need to find the sequence manually.

List all sequences in the database:

SELECT sequencename FROM pg_sequences WHERE schemaname = 'public';

Or check the column default to find which sequence it references:

SELECT column_default FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'id';

This might return nextval('users_id_seq'::regclass). Now find the current max and reset:

SELECT MAX(id) FROM users;
-- Let's say this returns 5042

SELECT setval('users_id_seq', 5042);

Verify the fix:

SELECT nextval('users_id_seq');
-- Should return 5043

Then reset it back (since nextval consumed one value):

SELECT setval('users_id_seq', 5042);

You can also check the current sequence value without advancing it:

SELECT last_value FROM users_id_seq;

Why this matters: This error is almost always caused by a desynchronized sequence — not by actual duplicate data. After a pg_restore, CSV import, or Django loaddata, the auto-increment sequence doesn’t know about the manually inserted IDs and starts generating values that already exist in the table.

Fix 3: Use ON CONFLICT DO NOTHING (Skip Duplicates)

If you’re inserting data and want to silently skip rows that would violate a unique constraint, use ON CONFLICT DO NOTHING:

INSERT INTO users (email, name)
VALUES ('[email protected]', 'John Doe')
ON CONFLICT (email) DO NOTHING;

This inserts the row only if no existing row has the same email. If a conflict occurs, the statement succeeds but inserts zero rows. No error is raised.

For bulk inserts, this is particularly useful:

INSERT INTO users (email, name)
VALUES
    ('[email protected]', 'John Doe'),
    ('[email protected]', 'Jane Smith'),
    ('[email protected]', 'Johnny Doe')  -- duplicate, will be skipped
ON CONFLICT (email) DO NOTHING;

You can also reference the constraint by name instead of column:

INSERT INTO users (email, name)
VALUES ('[email protected]', 'John Doe')
ON CONFLICT ON CONSTRAINT users_email_key DO NOTHING;

Fix 4: Use ON CONFLICT DO UPDATE (Upsert)

If you want to update the existing row when a conflict occurs instead of skipping or erroring, use ON CONFLICT DO UPDATE:

INSERT INTO users (email, name, updated_at)
VALUES ('[email protected]', 'John Doe', NOW())
ON CONFLICT (email) DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = EXCLUDED.updated_at;

EXCLUDED refers to the row that was proposed for insertion. This pattern is called an upsert — insert if new, update if existing.

You can add a WHERE clause to conditionally update:

INSERT INTO products (sku, price, updated_at)
VALUES ('WIDGET-01', 29.99, NOW())
ON CONFLICT (sku) DO UPDATE SET
    price = EXCLUDED.price,
    updated_at = EXCLUDED.updated_at
WHERE products.price <> EXCLUDED.price;

This only updates the row if the price actually changed, avoiding unnecessary writes and trigger firings.

This approach is essential when syncing data from external sources, where you may be processing the same record multiple times. It’s a similar defensive programming pattern to the ones used to avoid MySQL access denied errors — anticipating and handling expected failure modes gracefully.

Fix 5: Check for Duplicate Data Before Inserting

Sometimes the simplest approach is to check whether the row exists before trying to insert:

-- Only insert if no matching email exists
INSERT INTO users (email, name)
SELECT '[email protected]', 'John Doe'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE email = '[email protected]'
);

This works but has a subtle race condition: another transaction could insert the same email between your SELECT check and the INSERT. For this reason, ON CONFLICT (Fix 3 and Fix 4) is almost always preferred since it handles the check and insert atomically.

However, this pattern is still useful when you need more complex logic to decide whether to insert:

INSERT INTO audit_log (user_id, action, created_at)
SELECT u.id, 'login', NOW()
FROM users u
WHERE u.email = '[email protected]'
AND NOT EXISTS (
    SELECT 1 FROM audit_log
    WHERE user_id = u.id
    AND action = 'login'
    AND created_at > NOW() - INTERVAL '1 hour'
);

Fix 6: Use UUIDs Instead of Serial Integer IDs

If your application generates IDs at the application layer or in distributed systems, collisions with serial sequences are almost inevitable. Switching to UUIDs eliminates this class of errors entirely.

Enable the uuid-ossp extension (or use the built-in gen_random_uuid() in PostgreSQL 13+):

-- PostgreSQL 13+
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_number TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- PostgreSQL 12 and earlier
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_number TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

With UUIDs, you never need to worry about sequence synchronization. Each generated ID is practically guaranteed to be unique. The tradeoff is slightly larger storage (16 bytes vs. 4 or 8 bytes for integers) and slightly less human-readable IDs.

In Django, you would use UUIDField:

import uuid
from django.db import models

class Order(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    order_number = models.CharField(max_length=50)

Fix 7: Reset Sequences in Django and Rails

Django

Django manages sequences behind the scenes. After loading fixtures or importing data, the sequences often fall out of sync. Django provides a management command to fix this:

python manage.py sqlsequencereset myapp

This prints the SQL needed to reset sequences. To execute it directly:

python manage.py sqlsequencereset myapp | python manage.py dbshell

Or in a script:

from django.core.management import call_command
from io import StringIO

# Get the SQL
output = StringIO()
call_command('sqlsequencereset', 'myapp', stdout=output, no_color=True)
sql = output.getvalue()

# Execute it
from django.db import connection
with connection.cursor() as cursor:
    cursor.execute(sql)

If you’re seeing this error alongside other Django database issues, make sure your migrations are up to date first. A missing or outdated migration can cause structural problems — see Fix: Django OperationalError: no such table for related troubleshooting steps.

After running loaddata or any bulk import, always reset sequences:

python manage.py loaddata mydata.json
python manage.py sqlsequencereset myapp | python manage.py dbshell

Rails

Rails has a similar mechanism. Reset the primary key sequence for a model:

ActiveRecord::Base.connection.reset_pk_sequence!('users')

Or reset all sequences:

ActiveRecord::Base.connection.tables.each do |table|
  ActiveRecord::Base.connection.reset_pk_sequence!(table)
end

Fix 8: Fix Sequences After Bulk Import (COPY, pg_restore, CSV)

When you use COPY or pg_restore to load data with explicit IDs, the sequence is not automatically updated. This is the single most common cause of this error.

After COPY

COPY users (id, email, name) FROM '/path/to/users.csv' WITH (FORMAT csv, HEADER);

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

After pg_restore

If you restored from a pg_dump backup, sequences should be restored too. But if you restored only the data (using --data-only), the sequences won’t be set correctly:

pg_restore --data-only -d mydb backup.dump

Fix all sequences in one go:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT
            table_name,
            column_name,
            pg_get_serial_sequence(table_name, column_name) AS seq
        FROM information_schema.columns
        WHERE table_schema = 'public'
        AND column_default LIKE 'nextval%'
    LOOP
        EXECUTE format(
            'SELECT setval(%L, COALESCE(MAX(%I), 1)) FROM %I',
            r.seq, r.column_name, r.table_name
        );
    END LOOP;
END $$;

This loops through every table with a sequence-backed column and resets each sequence to the current maximum value. Run this after any bulk data import to prevent duplicate key errors on subsequent inserts. This is especially important when migrating data between databases or restoring partial backups.

Importing CSV without explicit IDs

If your CSV doesn’t include the ID column, let PostgreSQL generate it:

COPY users (email, name) FROM '/path/to/users.csv' WITH (FORMAT csv, HEADER);

By omitting the id column in the column list, PostgreSQL automatically calls nextval() on the sequence for each row, keeping everything in sync.

Pro Tip: After any bulk data import (COPY, pg_restore, Django loaddata), always reset sequences immediately. Run SELECT setval(pg_get_serial_sequence('table', 'id'), MAX(id)) FROM table; for each table. Make this part of your import script — not something you remember to do manually.

Fix 9: Handle Concurrent Insert Race Conditions

In high-concurrency environments, two transactions can attempt to insert the same unique value simultaneously. Even with proper sequence handling, this can happen with user-defined unique columns like email or order_number.

The classic race condition:

1. Transaction A checks: “Does email = '[email protected]' exist?” — No.
2. Transaction B checks: “Does email = '[email protected]' exist?” — No.
3. Transaction A inserts email = '[email protected]' — succeeds.
4. Transaction B inserts email = '[email protected]' — fails with duplicate key error.

The fix is to use ON CONFLICT (Fixes 3 and 4) or use advisory locks for more complex scenarios:

-- Advisory lock approach for complex insert-or-update logic
SELECT pg_advisory_xact_lock(hashtext('[email protected]'));

-- Now only one transaction at a time can proceed with this email
INSERT INTO users (email, name)
VALUES ('[email protected]', 'John Doe')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

In application code, catch the exception and retry:

import psycopg2
from psycopg2 import errors

max_retries = 3
for attempt in range(max_retries):
    try:
        cursor.execute(
            "INSERT INTO users (email, name) VALUES (%s, %s)",
            ('[email protected]', 'John Doe')
        )
        connection.commit()
        break
    except errors.UniqueViolation:
        connection.rollback()
        if attempt == max_retries - 1:
            raise

For Django applications, the retry pattern looks like this:

from django.db import IntegrityError

try:
    User.objects.create(email='[email protected]', name='John Doe')
except IntegrityError:
    user = User.objects.get(email='[email protected]')
    user.name = 'John Doe'
    user.save()

Or more cleanly with get_or_create and update_or_create:

# Insert if missing, return existing if found
user, created = User.objects.get_or_create(
    email='[email protected]',
    defaults={'name': 'John Doe'}
)

# Insert if missing, update if found
user, created = User.objects.update_or_create(
    email='[email protected]',
    defaults={'name': 'John Doe'}
)

These Django methods handle the race condition internally by catching IntegrityError and retrying. This same pattern of defensive database handling helps avoid issues like MongoDB ECONNREFUSED and Redis WRONGTYPE errors — always anticipate that database operations can fail and handle those failures gracefully.

Fix 10: Use Partial Unique Indexes

Sometimes you need uniqueness that applies only to a subset of rows. For example, you want each user to have only one “active” subscription, but they can have multiple “cancelled” ones.

A regular unique constraint doesn’t work here:

-- This prevents a user from having ANY two subscriptions, even cancelled ones
ALTER TABLE subscriptions ADD CONSTRAINT unique_user_subscription UNIQUE (user_id);

Use a partial unique index instead:

-- This only enforces uniqueness among active subscriptions
CREATE UNIQUE INDEX unique_active_subscription
ON subscriptions (user_id)
WHERE status = 'active';

Now a user can have one active subscription and any number of cancelled ones. If you try to insert a second active subscription for the same user, PostgreSQL raises the duplicate key error. But inserting another cancelled subscription works fine.

Partial unique indexes are also useful for soft-delete patterns:

-- Unique email, but only among non-deleted users
CREATE UNIQUE INDEX unique_email_active
ON users (email)
WHERE deleted_at IS NULL;

This lets you “delete” a user (by setting deleted_at) and then create a new user with the same email address.

When using partial unique indexes with ON CONFLICT, you must reference the index explicitly:

INSERT INTO subscriptions (user_id, plan, status)
VALUES (42, 'premium', 'active')
ON CONFLICT (user_id) WHERE status = 'active'
DO UPDATE SET plan = EXCLUDED.plan;

Still Not Working?

Sequence owned by wrong table

If you renamed or recreated a table, the sequence ownership might be broken. Check and fix it:

-- Check ownership
SELECT s.relname AS sequence, t.relname AS table, a.attname AS column
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid
JOIN pg_class t ON d.refobjid = t.oid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE s.relkind = 'S';

-- Fix ownership
ALTER SEQUENCE users_id_seq OWNED BY users.id;

Multiple unique constraints on the same table

Your table might have unique constraints you forgot about. List them all:

SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass AND contype = 'u';

Also check for unique indexes that aren’t tied to constraints:

SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'users' AND indexdef LIKE '%UNIQUE%';

Trigger or rule generating duplicate values

A BEFORE INSERT trigger might be modifying the incoming data in a way that creates duplicates. Check for triggers:

SELECT tgname, tgtype, proname
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE tgrelid = 'users'::regclass AND NOT tgisinternal;

Identity columns (PostgreSQL 10+)

If your table uses GENERATED ALWAYS AS IDENTITY instead of SERIAL, the reset syntax is slightly different:

-- Check the current identity sequence
SELECT column_name, column_default, is_identity, identity_generation
FROM information_schema.columns
WHERE table_name = 'users' AND is_identity = 'YES';

-- Reset the identity sequence
ALTER TABLE users ALTER COLUMN id RESTART WITH 5043;

Or dynamically:

DO $$
DECLARE
    max_id BIGINT;
BEGIN
    SELECT COALESCE(MAX(id), 0) + 1 INTO max_id FROM users;
    EXECUTE format('ALTER TABLE users ALTER COLUMN id RESTART WITH %s', max_id);
END $$;

The error happens only in tests

If your test suite runs into this error, it’s likely because test fixtures insert rows with explicit IDs, and the sequence isn’t reset between test runs. In Django, use TransactionTestCase with reset_sequences=True:

from django.test import TransactionTestCase

class MyTest(TransactionTestCase):
    reset_sequences = True

    def test_something(self):
        # Sequences are reset before this test runs
        pass

Note that reset_sequences=True makes tests slower because it requires resetting the database between tests rather than using transactions.

Related: If you’re dealing with other database connection and configuration errors, see Fix: PostgreSQL Connection Refused, Fix: MySQL Access Denied for User, and Fix: Django OperationalError: no such table. For NoSQL database issues, see Fix: MongoDB connect ECONNREFUSED and Fix: Redis WRONGTYPE Operation.