SQLSTATE error fieldbook

Diagnose constraints, transactions, privilege, resources, and connections with stable five-character codes

Applications branch on SQLSTATE, not error text that can vary by version and locale.

Frequent states

SQLSTATE	Name	Common meaning	Safe action
`23505`	unique_violation	Unique key conflict	Return conflict or use explicit `ON CONFLICT` semantics
`23503`	foreign_key_violation	Target absent or still referenced	Fix operation order; do not disable the constraint
`23502`	not_null_violation	Required column absent	Fix input or migration order
`23514`	check_violation	`CHECK` rejected the row	Explain boundary and correct the value
`22P02`	invalid_text_representation	Type conversion failed	Validate at the boundary and bind the right type
`40001`	serialization_failure	Isolation guarantee cannot be maintained	Roll back and retry the entire transaction
`40P01`	deadlock_detected	Wait cycle	Roll back whole transaction; normalize lock order
`55P03`	lock_not_available	`NOWAIT` or lock timeout	Retry later or return conflict
`57014`	query_canceled	Statement timeout or cancellation	Distinguish cancel from timeout; optimize or narrow
`25P02`	in_failed_sql_transaction	Earlier statement failed	`ROLLBACK`; do not continue business SQL
`42501`	insufficient_privilege	Action/object not granted	Fix grant/owner; do not jump to superuser
`42P01`	undefined_table	Missing table or wrong search path	Check database/schema/migration version
`42703`	undefined_column	Column absent	Check schema contract and deploy version
`53300`	too_many_connections	Connection slots exhausted	Inspect pools, leaks, and reserved admin access
`57P03`	cannot_connect_now	Starting, recovering, or shutting down	Bounded backoff and instance inspection
`08006`	connection_failure	Connection failed	Determine unknown commit state before safe retry

After a transaction error

An error inside a transaction normally leaves it aborted:

ERROR: current transaction is aborted...
SQLSTATE: 25P02

Issue ROLLBACK, or roll back to a savepoint created before the failure. More statements do not repair it automatically.

Retry classes

Retry the whole transaction: 40001, 40P01; bounded attempts, exponential backoff, and jitter.
Possibly transient: 55P03, 57P03, selected 08***; verify idempotency and unknown commit state.
Input/model errors—do not blind retry: 22***, 23***, 42***, 42501.
Resource conditions: 53300, disk full, memory pressure; retries amplify the incident. Shed load and repair capacity.

Record SQLSTATE, constraint/table/column fields, database and schema, application and migration versions, transaction/request id, parameter types with sensitive values redacted, and known commit state. Drivers usually expose structured fields; use them directly.

AI tool response

{
  "ok": false,
  "sqlstate": "23505",
  "category": "constraint",
  "retryable": false,
  "constraint": "customers_email_unique",
  "message_safe": "A customer with this email already exists"
}

Do not return raw database errors to end users without filtering. They may reveal object names, paths, or data fragments.

Frequent states

After a transaction error

Retry classes

Diagnostic context

AI tool response

On this page