- Software and digital services
- Databases
23000
PostgreSQL 23000 in databases: generic constraint violation
Evidence linked to this decision
View source: PostgreSQL 18 Appendix A: Error CodesRisk level
Medium
Proceed carefully and stop if the check is unclear.
Urgency
Medium
Quick answer
PostgreSQL 23000 (integrity_constraint_violation) indicates a write broke a Class 23 integrity rule without a narrower subcode. Check the error's schema, table, column, and constraint fields, roll back the aborted transaction, and fix the offending row before retrying.
Safety first
Stop and check this first
- Never drop a constraint, mark it `NOT ENFORCED`, or disable triggers in production merely to force a rejected row into the table; doing so leaves inconsistent data that can break queries and future `pg_dump` restores.
- Do not attempt to paste unredacted `Detail` lines or query parameters into external tickets before stripping passwords, connection strings, and personal data.
Safe checks you can perform
- Extract the structured object identifiers from ErrorResponse fields
- Decompile the named constraint from pg_constraint with pg_get_constraintdef
- Check whether deferrable constraints or SET CONSTRAINTS shifted the failure point
Leave these tasks to a qualified professional
- Do not attempt this professional task: Abort the failed transaction cleanly and reconcile the offending payload or default
- Do not attempt this professional task: Perform a controlled schema migration when the business invariant itself must change
What a technician must be able to justify
Technician visit
These are the steps they must carry out and show you. Tick them as they work: if steps are skipped, do not accept a part swap.
These are not instructions for opening or handling the equipment yourself. They are the checks that should support a technical conclusion.
Abort the failed transaction cleanly and reconcile the offending payload or default
What they doHave the application developer or DBA execute `ROLLBACK` on the aborted transaction block, compare the bound parameter values and column defaults against `pg_get_constraintdef(oid)`, and correct the application write logic so every row satisfies the Class 23 constraint.
They must show youthe decompiled constraint definition from `pg_get_constraintdef` alongside a test transaction that commits cleanly with valid input.
In writingthe exact constraint name (`n`), table name (`t`), root cause in the payload or default expression, and the verified fix.
Perform a controlled schema migration when the business invariant itself must change
What they doIf the constraint definition no longer matches valid business rules, have the DBA back up the affected table, test the `ALTER TABLE` constraint replacement inside a transaction in staging, and verify that all existing rows satisfy the updated rule before committing.
They must show youthe before-and-after `pg_constraint` catalog rows and the staging migration log.
In writingthe DDL script executed, the rollback plan, and the post-migration verification query output.
Before accepting a part or repair
Nothing has been shown yet. Do not accept a part swap based on the code alone.
Follow these steps
Note: Follow the documented order. An error code identifies the affected system, but it does not prove by itself which part has failed.
Extract the structured object identifiers from ErrorResponse fields
- Who:
- Your application developer or DBA inspects the structured driver error or verbose psql output.
- How:
- Enable `\set VERBOSITY verbose` in `psql` or read protocol fields `C` (Code), `s` (Schema name), `t` (Table name), `c` (Column name), `d` (Data type name), and `n` (Constraint name) from the failed statement.
- Result that matters:
- Appendix A guarantees complete object-name field coverage for SQLSTATE Class 23 errors.
- If field `n` names a specific constraint on table `t`, that confirms which database rule rejected the write and points to inspecting its exact catalog definition.
- If `s`, `t`, and `n` are empty while `C` is 23000, that suggests a user-defined function or client layer raised generic `integrity_constraint_violation` without populating object fields.
- Evidence to keep:
- Record the exact `C`, `M`, `D`, `s`, `t`, `c`, and `n` field values with passwords and personal data redacted.
Run read-only inspection only; never disable constraints to force a write through, and redact connection strings and personal data from saved logs.
Evidence: PostgreSQL 18 Appendix A: Error Codes · PostgreSQL error and notice fields · PostgreSQL bug reporting guidelines
Decompile the named constraint from pg_constraint with pg_get_constraintdef
- Who:
- Your DBA or developer with catalog read access runs a SELECT query against `pg_constraint`.
- How:
- Query `SELECT conname, contype, pg_get_constraintdef(oid, true) FROM pg_constraint WHERE conname = '<constraint_name>';` in a read-only session to see the constraint type (`c`, `f`, `n`, `p`, `u`, `t`, `x`) and reconstructed SQL.
- Result that matters:
- `pg_constraint` stores the `contype` code and `pg_get_constraintdef` reconstructs the creating command for the constraint.
- If `contype` and the decompiled expression show a rule that your input row violates, that confirms the payload conflicts with the live schema invariant.
- If the named constraint does not exist in the target schema, that rules out a table constraint in that schema and points to checking `current_schemas(true)` or domain constraints.
- Evidence to keep:
- Keep the query result showing `conname`, `contype`, and the `pg_get_constraintdef` text.
Do not alter or drop `pg_constraint` rows directly; issue `ROLLBACK;` first if your interactive session is still inside a failed transaction.
Evidence: PostgreSQL pg_constraint catalog · PostgreSQL 18 system information functions
Check whether deferrable constraints or SET CONSTRAINTS shifted the failure point
- Who:
- Your developer reviews the transaction block statements leading up to the 23000 error.
- How:
- Inspect `condeferrable` and `condeferred` in `pg_constraint` for the named rule, and check whether the session ran `SET CONSTRAINTS ... DEFERRED` or `IMMEDIATE` before the error appeared.
- Result that matters:
- `SET CONSTRAINTS` changes constraint checking between end-of-statement and transaction commit for deferrable `UNIQUE`, `PRIMARY KEY`, `REFERENCES`, and `EXCLUDE` rules.
- If the error occurs on `COMMIT` or `SET CONSTRAINTS ... IMMEDIATE`, that confirms an earlier write inside the same transaction left a violation that was checked retroactively.
- If the constraint is `NOT DEFERRABLE`, that rules out deferred checking and isolates the violation to the exact statement that failed.
- Evidence to keep:
- Save the transaction statement sequence alongside the `condeferrable` and `condeferred` flags.
Evidence: PostgreSQL pg_constraint catalog · PostgreSQL 18 SET CONSTRAINTS
Still not solved
If the error persists, stop here. See what the technician must do and show you.
Virtual technician
Does 23000 still appear after these checks?
This guided flow uses the exact code and your answers to choose a test, an expected result and the next branch.
Where this code applies
A code is meaningful only inside the right product and version context.
Primary scope
- System
- Software and digital services
- Brand
- PostgreSQL
- Product type
- Databases
Known code variants
- integrity_constraint_violation
- PostgreSQL 23000
- SQLSTATE 23000
What it means
In Appendix A of the PostgreSQL server documentation, SQLSTATE 23000 is the standard 000 code for Class 23 (Integrity Constraint Violation). PostgreSQL emits a class-ending 000 code only when an integrity failure belongs to Class 23 but is not mapped to a narrower condition such as 23001, 23502, 23503, 23505, 23514, or 23P01. For Class 23 errors, the protocol ErrorResponse includes separate schema, table, column, data type, and constraint name fields (`s`, `t`, `c`, `d`, `n`). Receiving 23000 alone does not prove which table constraint failed or whether the root cause lies in client input, a trigger, or a schema rule.
Warnings and stop conditions
- Mediumsource verified
Never drop a constraint, mark it `NOT ENFORCED`, or disable triggers in production merely to force a rejected row into the table; doing so leaves inconsistent data that can break queries and future `pg_dump` restores.
Evidence: PostgreSQL constraints · PostgreSQL 18 CREATE TABLE
- Lowsource verified
Do not attempt to paste unredacted `Detail` lines or query parameters into external tickets before stripping passwords, connection strings, and personal data.
Evidence: PostgreSQL error and notice fields · PostgreSQL bug reporting guidelines
Symptoms and causes
Probable causes · in order
- An integrity failure occurred that is not assigned to a narrower Class 23 subcodeCommon
Appendix A specifies that the 000 code in an error class is reserved for conditions within that class that do not have a more specific SQLSTATE assigned, or when a custom PL/pgSQL RAISE or driver maps a constraint failure to the generic integrity_constraint_violation condition.
Evidence: PostgreSQL 18 Appendix A: Error Codes
- A row write or default expression violated a table or domain constraintPossible
Section 5.5 explains that if a statement attempts to store column data that violates a declared constraint, PostgreSQL raises an error even when the rejected value originated from a column default definition.
Evidence: PostgreSQL constraints
- A deferred constraint or constraint trigger failed when checked inside the transactionPossible
The pg_constraint catalog stores table and domain constraints along with user-defined constraint triggers created with CREATE CONSTRAINT TRIGGER, any of which can reject a data change during statement execution or transaction commit.
Evidence: PostgreSQL pg_constraint catalog · PostgreSQL 18 SET CONSTRAINTS
Sources and technical references
- 1
PostgreSQL 18 Appendix A: Error Codes
PostgreSQL Global Development Group · 2026 · official_doc
- 2
PostgreSQL Global Development Group · 2026 · official_doc
Was this entry useful?