PostgreSQL Integrity Constraint Violation (SQLSTATE 23000)

When a DML statement violates a database constraint, PostgreSQL raises an error in SQLSTATE class 23 — Integrity Constraint Violation. The base code 23000 (integrity_constraint_violation) is the generic parent condition; in practice you will almost always see a more specific sub-code such as 23502, 23503, or 23505. The error appears in logs and client output as ERROR: <description of the violated constraint>.

What This Error Means

SQLSTATE class 23 covers all constraint enforcement failures. PostgreSQL defines it as the parent class for any situation where a row being inserted or updated would leave the database in a state that violates a declared constraint. The condition name integrity_constraint_violation maps to the exact code 23000 and acts as the catch-all when no more specific sub-code applies — which is rare in normal usage.

When any error in class 23 is raised, PostgreSQL immediately aborts the current statement and marks the surrounding transaction as aborted. No further SQL can execute in that transaction until it is rolled back. This is by design: the engine refuses to let an invalid row land in the table, and the transaction must be explicitly cleaned up before a new one can begin.

Because 23000 itself is the parent class, PostgreSQL's condition hierarchy means that catching integrity_constraint_violation in application code (or a PL/pgSQL EXCEPTION block) will also catch all of its sub-conditions. This makes it a useful broad handler, but you should generally catch the specific sub-code so you can distinguish between, for example, a duplicate-key conflict that can be retried versus a foreign key violation that signals a logic error.

Common Causes

1. Catching the base class instead of a specific sub-code. Application code or a PL/pgSQL handler that explicitly raises or re-raises SQLSTATE '23000' rather than the specific child code. This is unusual but can appear in custom trigger or wrapper functions.
2. A driver or ORM surfacing the parent class. Some older JDBC drivers or ORM layers collapse all class-23 errors into the generic 23000 code before passing them to application code, hiding the specific sub-code.
3. An extension or procedural language raising a generic constraint error. A custom C extension or foreign data wrapper that signals a constraint failure without specifying which sub-type triggered it.
4. Reviewing logs without the full detail line. Log aggregators that truncate PostgreSQL error messages may show only the SQLSTATE code; the actual sub-code and constraint name appear in the DETAIL field of the full error.

How to Fix integrity_constraint_violation

1. Identify the specific sub-code. In most cases what you actually hit is one of the named sub-conditions. Check the full error output or the SQLSTATE field reported by your driver:

   SQLSTATE	Condition name	Trigger
   23001	restrict_violation	DELETE/UPDATE blocked by a RESTRICT foreign key
   23502	not_null_violation	Column declared NOT NULL received a NULL
   23503	foreign_key_violation	Referenced row does not exist (or is being deleted)
   23505	unique_violation	Duplicate value in a UNIQUE or PRIMARY KEY column
   23514	check_violation	Row failed a CHECK constraint
   23P01	exclusion_violation	Row conflicts with an existing row under an exclusion constraint

2. Catch the correct sub-code in application code. Rather than catching the generic class, target the specific condition:

   # psycopg2 example
   import psycopg2
   from psycopg2 import errors
   
   try:
       cur.execute("INSERT INTO orders (...) VALUES (...)")
   except errors.UniqueViolation:
       # handle duplicate key — e.g., UPDATE instead
       pass
   except errors.ForeignKeyViolation:
       # handle missing parent row
       pass
   except errors.IntegrityConstraintViolation:
       # fallback for any other class-23 error
       raise
   

3. Catch it in PL/pgSQL. Use the condition name directly:

   DO $$
   BEGIN
     INSERT INTO orders (id, customer_id) VALUES (1, 999);
   EXCEPTION
     WHEN foreign_key_violation THEN
       RAISE NOTICE 'Customer 999 does not exist';
     WHEN integrity_constraint_violation THEN
       RAISE;  -- re-raise anything else in class 23
   END;
   $$;
   

4. Inspect the full error detail. PostgreSQL reports constraint violations with a DETAIL line naming the exact constraint and the offending value. Always log or expose this detail in development; never swallow it silently.

   -- psql shows:
   ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
   DETAIL:  Key (customer_id)=(999) is not present in table "customers".
   

5. Update your driver or ORM. If your framework collapses all class-23 errors into 23000, upgrading often restores the specific sub-code, which lets you handle cases individually.

Additional Information

• SQLSTATE class 23 has been part of the SQL standard and PostgreSQL since version 7.x. The specific sub-codes like 23505 and 23503 are the ones you will encounter in virtually all real workloads.
• All class-23 errors cause an immediate statement-level error; the transaction is left in a failed state and must be rolled back before any further work.
• In JDBC, the SQLException.getSQLState() method returns the five-character SQLSTATE string. Spring's DataIntegrityViolationException wraps all class-23 errors without distinguishing sub-types by default — check the cause chain for the original PSQLException and call getServerErrorMessage().getSQLState() for the specific code.
• Hibernate maps ConstraintViolationException to class-23 errors but similarly does not distinguish sub-codes at the ORM layer.
• Using ON CONFLICT clauses (INSERT ... ON CONFLICT DO NOTHING / DO UPDATE) is the idiomatic way to handle 23505 unique violations without a round-trip exception; there is no equivalent for other class-23 sub-codes.

Frequently Asked Questions

Why do I see 23000 instead of 23505 or 23503? The 23000 code appears when something in the call stack — a driver, an ORM, a PL/pgSQL wrapper, or a C extension — raises or re-raises the generic parent class rather than the specific sub-code. Check whether your driver version collapses sub-codes, and inspect the full error message text and DETAIL field which will usually name the specific constraint that was violated.

Does catching integrity_constraint_violation also catch unique_violation and foreign_key_violation? Yes. PostgreSQL's condition hierarchy makes integrity_constraint_violation (23000) the parent of all class-23 sub-conditions. In PL/pgSQL EXCEPTION blocks and in drivers that understand the hierarchy (such as psycopg2), catching the parent condition catches all children. This is useful as a fallback handler but should not be the only handler if you need to distinguish between recoverable conflicts (unique violations) and logic errors (foreign key violations).

Can I raise SQLSTATE '23000' from my own PL/pgSQL code? Yes, but you should use a specific sub-code instead so callers can handle it precisely. If you want to signal a generic constraint failure from a trigger or stored procedure, RAISE EXCEPTION USING ERRCODE = '23000' works, but ERRCODE = '23505' or another sub-code is almost always more appropriate.

Will a 23000 error roll back my entire transaction? The error aborts the current statement and marks the transaction as failed (you will see ERROR: current transaction is aborted, commands ignored until end of transaction block for any subsequent statement). The transaction is not automatically rolled back — your application must issue ROLLBACK (or let the connection reset). If you want only the failing statement to be rolled back, use a SAVEPOINT before the statement and roll back to it on error.