ETL pipeline runs twice (e.g., at-least-once delivery) loading duplicate records. Merge/upsert logic not implemented — plain INSERT used instead of INSERT ... ON CONFLICT. Source data itself contains duplicates that were not deduplicated upstream. Sequence reset or ID collision after a restore/migration

Step 1: Use `INSERT ... ON CONFLICT (key_column) DO UPDATE SET ...` (upsert) or `DO NOTHING`.. Step 2: Add a deduplication step before the insert: `SELECT DISTINCT ON (key_col) * FROM staging`.. Step 3: Ensure the pipeline is idempotent — re-running should not create duplicates.. Step 4: Audit source data for duplicates with `SELECT key_col, COUNT(*) FROM source GROUP BY 1 HAVING COUNT(*) > 1`.

High severitydata format

Power BI Refresh Error:
23505

What does this error mean?

An INSERT or UPDATE attempted to store a duplicate value in a column or index with a UNIQUE constraint.

Common causes

1ETL pipeline runs twice (e.g., at-least-once delivery) loading duplicate records
2Merge/upsert logic not implemented — plain INSERT used instead of INSERT ... ON CONFLICT
3Source data itself contains duplicates that were not deduplicated upstream
4Sequence reset or ID collision after a restore/migration

How to fix it

1Step 1: Use `INSERT ... ON CONFLICT (key_column) DO UPDATE SET ...` (upsert) or `DO NOTHING`.
2Step 2: Add a deduplication step before the insert: `SELECT DISTINCT ON (key_col) * FROM staging`.
3Step 3: Ensure the pipeline is idempotent — re-running should not create duplicates.
4Step 4: Audit source data for duplicates with `SELECT key_col, COUNT(*) FROM source GROUP BY 1 HAVING COUNT(*) > 1`.

Frequently asked questions

How do I prevent 23505 errors in automated pipelines?

Add retry logic with exponential backoff, validate data quality before loading, and monitor pipeline failures in MetricSign to catch this error early.

Official documentation: https://www.postgresql.org/docs/current/errcodes-appendix.html