40002: transaction_integrity_constraint_violation

What does it mean?

The SQL-standard code for an integrity constraint checked at end of transaction being violated at COMMIT. Important: PostgreSQL does not produce this code in practice; it returns deferred (DEFERRED) constraint violations at COMMIT with the constraint's own code: 23503 for foreign keys, 23505 for uniqueness.

Why does it happen?

A foreign key or unique constraint defined DEFERRABLE INITIALLY DEFERRED was temporarily broken in intermediate steps of the transaction and not fixed by COMMIT time; in PostgreSQL this is reported as 23503/23505. For example, inserting mutually referencing rows in the wrong order without fixing them causes this.

How to fix it

Fix the transaction logic so that all deferred constraints are satisfied before COMMIT (add missing references, resolve conflicts). If needed, check the constraint early within the transaction with SET CONSTRAINTS ... IMMEDIATE to catch the problematic step sooner. Reorder inserts/updates for mutual references.