PostgreSQL Error:
23505

What does this error mean?

PostgreSQL gooit error 23505 wanneer een INSERT of UPDATE een waarde probeert op te slaan die al bestaat in een kolom of index met een UNIQUE constraint. In een data-pipeline betekent dit concreet: de betreffende rij wordt geweigerd en de transactie rolt terug — tenzij je ON CONFLICT hebt gedefinieerd. Het symptoom is een harde SQLSTATE 23505-fout in je pipeline-log, vaak gecombineerd met een rollback van de batch. Bij at-least-once pipelines (ADF, Fivetran, dbt incremental zonder unieke sleutel) is dit een van de meest voorkomende oorzaken van stille dataverlies: de pipeline meldt 'succeeded' terwijl rijen stil zijn overgeslagen.

Common causes

• 1At-least-once ETL-delivery zonder idempotentie: ADF Copy Activity of Fivetran herlaadt een batch na een timeout. De eerste run slaagde al deels, de tweede run probeert dezelfde primary keys opnieuw te inserteren en botst op de UNIQUE constraint.
• 2Plain INSERT in plaats van upsert: een dbt-model of custom SQL gebruikt `INSERT INTO target SELECT ... FROM staging` zonder `ON CONFLICT`-clausule. Bij elke incrementele run worden duplicaten geprobeerd zodra de brondata al eerder geladen is.
• 3Duplicaten in brondata die niet upstream gefilterd zijn: het bronsysteem levert rijen met dezelfde business key (bijv. `order_id`) door een retransmissie, CDC fanout, of API-paginering die een pagina dubbel teruggeeft.
• 4Sequence- of ID-collision na restore of migratie: na een `pg_dump`/`pg_restore` of een cross-regio migratie is de sequence niet opnieuw gesynchroniseerd met de hoogste bestaande waarde. Nieuwe inserts genereren IDs die al bestaan: `SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));` was overgeslagen.
• 5Gelijktijdige schrijvers zonder locking: meerdere workers of pipeline-threads proberen tegelijkertijd dezelfde sleutel te inserteren. De eerste slaagt, de rest stoot op 23505. Typisch bij parallelle ADF-partities of multi-threaded Python-loaders zonder `INSERT ... ON CONFLICT`.
• 6dbt incremental model met `unique_key` maar zonder de juiste merge-strategie: bij sommige adapters valt dbt terug op DELETE+INSERT. Als de DELETE niet volledig uitvoert (bijv. door een filter-bug), blijven oude rijen staan en botst de INSERT.
• 7Handmatige backfill over een periode die al geladen is: een engineer draait een historische laadjob opnieuw zonder eerst de overlappende periode te truncaten.

How to fix it

1. 1Stap 1 — Diagnosticeer welke kolom en waarde botsen. Lees de volledige foutmelding: `ERROR: duplicate key value violates unique constraint "orders_pkey" DETAIL: Key (id)=(42317) already exists.` De constraint-naam en de conflicterende waarde staan in DETAIL.
2. 2Stap 2 — Vervang plain INSERT door een upsert: `INSERT INTO target (id, amount, updated_at) SELECT id, amount, updated_at FROM staging ON CONFLICT (id) DO UPDATE SET amount = EXCLUDED.amount, updated_at = EXCLUDED.updated_at;` Gebruik DO NOTHING als je bestaande rijen nooit wilt overschrijven.
3. 3Stap 3 — Dedupliceer de staging-tabel vóór de insert: `INSERT INTO target SELECT DISTINCT ON (id) * FROM staging ORDER BY id, updated_at DESC ON CONFLICT (id) DO NOTHING;` Dit vangt duplicaten op die al in de staging zitten voordat ze de constraint raken.
4. 4Stap 4 — Controleer de brondata op duplicaten met: `SELECT id, COUNT(*) FROM staging GROUP BY 1 HAVING COUNT(*) > 1 ORDER BY 2 DESC LIMIT 20;` Als er hits zijn, trace terug naar het bronsysteem of de extractie-query.
5. 5Stap 5 — Fix een sequence-mismatch na migratie: `SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders) + 1, false);` Controleer daarna: `SELECT last_value FROM orders_id_seq;`
6. 6Stap 6 — Maak de pipeline idempotent: voeg een expliciete deduplicatiestap toe aan het begin van je pipeline (bijv. in een dbt `staging`-model met `ROW_NUMBER() OVER (PARTITION BY id ORDER BY loaded_at DESC) = 1`) zodat herdraaien altijd veilig is.
7. 7Stap 7 — Stel monitoring in op 23505-fouten in je pipeline-orchestrator. In ADF: voeg een Failure-pad toe op de Copy Activity en log naar een centrale error-tabel. In dbt: gebruik `store_failures: true` op het unique-test zodat conflicterende keys bewaard blijven voor analyse.

Example log output

ERROR: duplicate key value violates unique constraint "orders_pkey"
DETAIL: Key (id)=(42317) already exists.
CONTEXT: COPY orders, line 1: "42317,EUR,199.95,2026-05-10"

Frequently asked questions

Other data format errors