Fix "duplicate key value violates unique constraint" After a Restore

Problem statement

After loading fixtures, restoring a partial backup, or importing data from SQLite or MySQL, creating new objects fails:

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

Each new save tries id=1, then id=2, and so on, failing until it passes the highest existing ID. The rows were inserted with explicit IDs, which PostgreSQL accepts, but the sequence that generates new IDs was never advanced. It still thinks the next value is 1. Resetting the sequence to the current maximum fixes it instantly.

1. Generate reset SQL for affected apps: python manage.py sqlsequencereset blog shop accounts.
2. Run it: python manage.py sqlsequencereset blog | python manage.py dbshell.
3. For a single table: SELECT setval(pg_get_serial_sequence('blog_post', 'id'), COALESCE(MAX(id), 1)) FROM blog_post;.
4. Reset sequences for every app after any bulk load with explicit IDs.
5. Include sequence data in backups by restoring full dumps rather than data-only table copies.

Step-by-step solution

1. Confirm it is the sequence

Compare the sequence position with the table's maximum ID:

SELECT MAX(id) FROM blog_post;
SELECT last_value, is_called FROM blog_post_id_seq;

If last_value is below MAX(id), the sequence is behind. On Django 4.1+ with identity columns or older serial columns, find the sequence name reliably with:

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

2. Reset with sqlsequencereset

Django generates the right SQL for every model in the listed apps:

python manage.py sqlsequencereset blog shop accounts

BEGIN;
SELECT setval(pg_get_serial_sequence('"blog_post"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "blog_post";
COMMIT;

Pipe it straight into the database:

python manage.py sqlsequencereset blog shop accounts | python manage.py dbshell

To reset every installed app, including auth and contenttypes:

python manage.py sqlsequencereset $(python manage.py shell -c "from django.apps import apps; print(' '.join(a.label for a in apps.get_app_configs()))") | python manage.py dbshell

3. Fix one table by hand

For a quick fix on a single table:

SELECT setval(
  pg_get_serial_sequence('blog_post', 'id'),
  COALESCE((SELECT MAX(id) FROM blog_post), 1),
  (SELECT MAX(id) FROM blog_post) IS NOT NULL
);

The third argument controls whether the next nextval returns the value plus one (true) or the value itself (false, for an empty table).

4. Know which operations cause it

• loaddata with fixtures that include pk values. Django resets sequences automatically after loaddata on PostgreSQL in recent versions, but not after custom import scripts.
• pg_dump --data-only -t table restored into an existing schema without the sequence.
• Migrating from SQLite or MySQL with dumpdata/loaddata or a tool that copies rows but not sequences.
• Raw INSERT ... (id, ...) VALUES (...) in scripts or data migrations.
• bulk_create with explicit primary keys.

5. Prevent it next time

Restore complete dumps (pg_dump -Fc and pg_restore), which include SELECT pg_catalog.setval(...) statements for every sequence. In import scripts and data migrations that set IDs, finish with a sequence reset:

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

def reset_sequences(*app_labels):
    sql = StringIO()
    call_command("sqlsequencereset", *app_labels, stdout=sql, no_color=True)
    with connection.cursor() as cur:
        cur.execute(sql.getvalue())

Verification check: in a Django shell, create and delete a test object for each affected model; no IntegrityError should appear. Re-run the last_value versus MAX(id) comparison for the main tables.

Explanation

A PostgreSQL serial or identity column takes its default from a sequence object via nextval(). The sequence is independent of the table: inserting a row with an explicit ID does not call nextval, so the sequence does not move. That is deliberate, because it allows restoring data with original IDs, but it means whoever inserts explicit IDs is responsible for moving the sequence afterwards. Full dumps do this automatically; ad-hoc copies do not.

Edge cases / notes

• Other unique constraints. If the duplicate key is not id (for example slug or email), this is a real duplicate in your data, not a sequence problem.
• UUID primary keys have no sequence and never show this issue.
• Concurrent inserts. Run the reset when writes are paused, or the maximum can move while you set it; setval itself is safe to repeat.
• Multiple databases. Pass --database to sqlsequencereset for non-default connections.

Next steps

• Restore safely with Backup and Restore PostgreSQL for Django.
• Review database hygiene in the Django PostgreSQL Production Checklist.

FAQ

Why does the error mention id=1?

The sequence was never advanced, so it hands out 1, 2, 3 and so on, which already exist.

Is it safe to run sqlsequencereset in production?

Yes. It only moves sequences to the current maximum and does not touch rows.

Does loaddata reset sequences?

On PostgreSQL, Django resets sequences after loaddata. Custom scripts and raw SQL do not.

Will retrying the save eventually work?

Each failed attempt consumes one sequence value, so it will after enough retries, but resetting is the correct fix.