Duplicate rows in the source data or staging table being loaded without deduplication — common when a pipeline retries after a partial failure and reprocesses already-loaded rows.. Foreign key reference to a parent row that does not exist yet because the parent table load ran after the child table load, or the parent row was deleted between extraction and loading.. NOT NULL column receiving NULL values from the source because an upstream schema change added a new required field that the extraction query does not yet include.. CHECK constraint violated by out-of-range values — for example a percentage column constrained to 0–100 receiving a raw count value from the source.. Concurrent pipeline runs inserting the same natural key simultaneously, causing a race condition on a unique index that neither run anticipated.. Exclusion constraint conflict (range overlap) on partitioned tables, especially with time-series data where partition boundaries shift during a schema migration.

Step 1: Identify the exact constraint. Run: SELECT conname, contype, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'your_table'::regclass; — this shows all constraints on the target table with their definitions.. Step 2: For unique constraint violations, add an upsert clause: INSERT INTO target_table (...) VALUES (...) ON CONFLICT (key_column) DO UPDATE SET col = EXCLUDED.col; — or use ON CONFLICT DO NOTHING if duplicates should be silently skipped.. Step 3: For foreign key violations, verify parent data exists before loading child rows: SELECT id FROM parent_table EXCEPT SELECT parent_id FROM staging_child_table; — any results indicate missing parents that need to be loaded first.. Step 4: For NOT NULL violations, add a COALESCE or default in your transformation layer: SELECT COALESCE(source_col, 'default_value') AS target_col FROM staging; — or fix the extraction query to include the missing column.. Step 5: For CHECK constraint violations, query the offending rows: SELECT * FROM staging_table WHERE NOT (check_expression); — replace check_expression with the constraint definition from Step 1.. Step 6: For concurrent load race conditions, wrap the insert in an advisory lock: SELECT pg_advisory_xact_lock(hashtext('your_table_load')); before the INSERT to serialize overlapping pipeline runs.. Step 7: After fixing, validate with a dry run: BEGIN; INSERT INTO target_table SELECT * FROM staging_table; -- inspect results, then ROLLBACK; — this confirms the fix without committing data.

High severitydata format

PostgreSQL Error:
23000

What does this error mean?

SQLSTATE 23000 is the parent class for all integrity constraint violations in PostgreSQL. When the server returns this code, an INSERT, UPDATE, or DELETE statement conflicted with a unique constraint, foreign key constraint, CHECK constraint, or NOT NULL restriction on the target table. In data pipelines (ADF, dbt, Airflow, custom Python ETL), this error typically surfaces during the load phase and causes the entire transaction or batch to roll back. The symptom you see: the pipeline run fails, the error log contains 'integrity_constraint_violation' with the specific constraint name, and zero rows from that batch make it into the destination. Downstream tables that depend on this load remain stale until the constraint conflict is resolved and the pipeline is re-run.

Common causes

1Duplicate rows in the source data or staging table being loaded without deduplication — common when a pipeline retries after a partial failure and reprocesses already-loaded rows.
2Foreign key reference to a parent row that does not exist yet because the parent table load ran after the child table load, or the parent row was deleted between extraction and loading.
3NOT NULL column receiving NULL values from the source because an upstream schema change added a new required field that the extraction query does not yet include.
4CHECK constraint violated by out-of-range values — for example a percentage column constrained to 0–100 receiving a raw count value from the source.
5Concurrent pipeline runs inserting the same natural key simultaneously, causing a race condition on a unique index that neither run anticipated.
6Exclusion constraint conflict (range overlap) on partitioned tables, especially with time-series data where partition boundaries shift during a schema migration.

How to fix it

1Step 1: Identify the exact constraint. Run: SELECT conname, contype, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'your_table'::regclass; — this shows all constraints on the target table with their definitions.
2Step 2: For unique constraint violations, add an upsert clause: INSERT INTO target_table (...) VALUES (...) ON CONFLICT (key_column) DO UPDATE SET col = EXCLUDED.col; — or use ON CONFLICT DO NOTHING if duplicates should be silently skipped.
3Step 3: For foreign key violations, verify parent data exists before loading child rows: SELECT id FROM parent_table EXCEPT SELECT parent_id FROM staging_child_table; — any results indicate missing parents that need to be loaded first.
4Step 4: For NOT NULL violations, add a COALESCE or default in your transformation layer: SELECT COALESCE(source_col, 'default_value') AS target_col FROM staging; — or fix the extraction query to include the missing column.
5Step 5: For CHECK constraint violations, query the offending rows: SELECT * FROM staging_table WHERE NOT (check_expression); — replace check_expression with the constraint definition from Step 1.
6Step 6: For concurrent load race conditions, wrap the insert in an advisory lock: SELECT pg_advisory_xact_lock(hashtext('your_table_load')); before the INSERT to serialize overlapping pipeline runs.
7Step 7: After fixing, validate with a dry run: BEGIN; INSERT INTO target_table SELECT * FROM staging_table; -- inspect results, then ROLLBACK; — this confirms the fix without committing data.

Example log output

ERROR:  duplicate key value violates unique constraint "orders_order_id_key"
DETAIL:  Key (order_id)=(10042) already exists.
STATEMENT:  INSERT INTO orders (order_id, customer_id, amount) VALUES ($1, $2, $3)

Frequently asked questions

How do I prevent 23000 errors in automated pipelines?

Use ON CONFLICT (upsert) clauses for unique constraints, enforce load ordering (parents before children) for foreign keys, and add data validation in your staging layer before the final INSERT. Monitor pipeline runs in MetricSign to catch recurring violations before they become chronic.

What is the difference between SQLSTATE 23000 and more specific codes like 23505 or 23503?

23000 is the parent class for all integrity constraint violations. PostgreSQL usually returns a more specific subclass: 23505 for unique_violation, 23503 for foreign_key_violation, 23502 for not_null_violation, and 23514 for check_violation. If you see the generic 23000, your client library or ORM may be truncating the full SQLSTATE — check the raw server log for the 5-digit code.

Can I retry a pipeline after a 23000 error, or will it fail again?

Retrying without changes will produce the same error, because the constraint conflict is deterministic — the same data hits the same constraint. Fix the source data or add conflict-handling logic first. The one exception: if a concurrent transaction held a lock that caused a transient conflict, a retry may succeed, but this is rare for 23000 (more common with 40001 serialization failures).

How do I find which specific rows caused the 23000 error?

The PostgreSQL error message includes the constraint name and usually the conflicting key values. To find all offending rows in bulk, load your data into a staging table first, then run a query that checks the constraint condition — for example: SELECT s.* FROM staging s JOIN target t ON s.key_col = t.key_col; for unique violations, or SELECT s.* FROM staging s LEFT JOIN parent p ON s.fk_col = p.id WHERE p.id IS NULL; for foreign key violations.

Source · www.postgresql.org/docs/current/errcodes-appendix.html