postgres ERROR: duplicate key value violates unique constraint during backfill re-run

postgres ERROR: duplicate key value violates unique constraint during backfill re-run

TL;DR: The backfill re-ran over rows it already wrote - it is not idempotent. Add ON CONFLICT DO NOTHING (or DO UPDATE) to the backfill INSERT so re-runs skip existing rows, and track completed batches in a checkpoint table so a re-run skips finished work entirely.

postgres ERROR: duplicate key value violates unique constraint during backfill re-run

Steps

1. Identify the conflicting rows: the error names the constraint and the key value. Check whether those rows came from the first backfill run. Expected: confirmed - the re-run is re-inserting its own earlier output, not bad source data.

2. Make the write idempotent:

   INSERT INTO target (id, data)
   SELECT id, data FROM source_batch
   ON CONFLICT (id) DO NOTHING;

   Use DO UPDATE instead of DO NOTHING if a re-run should refresh changed values. Expected: re-running the same batch inserts zero new rows and raises no error.

3. Add a checkpoint: record completed batch ids in a small table and skip batches already marked done at the start of each run. Expected: a second full run finishes quickly with every batch skipped.

4. Re-run the backfill. Expected: completes with no duplicate-key errors; the target holds each source row exactly once.

Use this when

• Duplicate-key errors appear on the second (or later) run of a backfill or data migration.
• The first run partially completed before stopping.
• The conflicting rows match what the earlier run wrote.

Not for this skill when

• Duplicate-key errors appear on the FIRST run - the source data itself has duplicates; dedupe the source.
• Application writes race the backfill and collide - that is a locking/coordination problem, not backfill idempotency.
• You do not recognize the constraint name - find which table and key it guards before changing the write.

Variant phrasings

• duplicate key violates unique constraint backfill rerun
• postgres on conflict do nothing backfill idempotent
• data migration rerun duplicate rows

Why it happens

Without a conflict policy or a checkpoint, every re-run replays the same INSERT statements from the beginning. The unique constraint correctly refuses the second copy of each row - the error is the backfill's lack of idempotency, not corrupt data.

Edge cases

• DO NOTHING silently skips rows that changed upstream since the first run - use DO UPDATE when freshness matters.
• The checkpoint table needs its own transaction discipline: a checkpoint row that never commits recreates the double-run bug you just fixed.
• For very large backfills, keep each ON CONFLICT statement to one batch so a single statement never grows unbounded.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_DZHWzJ1XVZoOyyTFqpsXbw