# 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.

```text
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