SaaS architecture
PostgreSQL error codes (SQLSTATE), with measured causes
Over thirty PostgreSQL errors by SQLSTATE — 23505, 42501, 42P01, 42703, 25P02, 40001 and more — each with the cause we reproduced and the measurement.
Every PostgreSQL error carries a five-character SQLSTATE: 23505 is a duplicate key, 42501 a missing privilege, 42P01 a table that cannot be found. The code is what your driver exposes (e.pgcode in psycopg2, e.sqlstate in psycopg 3, SQLState in JDBC) and what a retry loop should look at, because the message text changes between versions and the code does not. The first two characters are the class: 23 integrity, 25 transaction state, 40 “roll back and retry”, 42 syntax or access.
Below are the errors we reproduced for the articles on this site — on PostgreSQL 17, most also on 16 and 18 — by code, with the first cause to check and the article where each is measured. The articles reproduce causes; none of them counts how often each occurs in the wild. The condition names are from PostgreSQL’s own list of error codes.
Which SQLSTATE means what?
| SQLSTATE | Condition name | Message | First cause to check | Article |
|---|---|---|---|---|
0A000 | feature_not_supported | cannot alter type of a column used in a policy definition (or … by a view or rule) | widening a column that a policy, view or generated column uses | value too long |
0A000 | feature_not_supported | COPY FROM not supported with row-level security | restoring one tenant’s data as a role subject to row-level security | pg_dump one tenant |
21000 | cardinality_violation | more than one row returned by a subquery used as an expression | a lookup that stopped being unique, often a key that is unique per tenant queried without the tenant | more than one row returned by a subquery |
22001 | string_data_right_truncation | value too long for type character varying(n) | a value longer than the limit; the message names no column | value too long |
22021 | character_not_in_repertoire | invalid byte sequence for encoding “UTF8”: 0x… | a NUL byte, or a Windows-1252 or Latin-1 file loaded as UTF-8 | invalid byte sequence |
22P02 | invalid_text_representation | invalid input syntax for type uuid: "" | an empty string, or a missing value turned into text ('undefined', 'null'), cast to uuid — a real NULL gives no error | invalid input syntax for type uuid |
23502 | not_null_violation | null value in column “id” … violates not-null constraint | the id column lost its default, or the insert sends NULL | null value in column “id” |
23503 | foreign_key_violation | insert or update on table … violates foreign key constraint (or update or delete on table … , still referenced) | a child inserted before its parent, or a parent deleted while children point at it; on PostgreSQL 18 an ON DELETE RESTRICT key raises 23001 instead | violates foreign key constraint |
23505 | unique_violation | duplicate key value violates unique constraint ”…_pkey” | a sequence behind the table after an import | duplicate key value |
25P01 | no_active_sql_transaction | SET LOCAL can only be used in transaction blocks (a warning) | SET LOCAL in autocommit, so the setting never applies; never raised as an exception, only in the notices or the log | PgBouncer and row-level security |
25P02 | in_failed_sql_transaction | current transaction is aborted, commands ignored until end of transaction block | an earlier error in the same transaction was caught and ignored | current transaction is aborted |
25P03 | idle_in_transaction_session_timeout | terminating connection due to idle-in-transaction timeout | a transaction left open while the application did something else | idle in transaction |
25P04 | transaction_timeout | terminating connection due to transaction timeout | transaction_timeout (PostgreSQL 17) reached, also by one long statement | idle in transaction |
25006 | read_only_sql_transaction | cannot execute … in a read-only transaction | a write in a session or role set to default_transaction_read_only | read-only user |
28000 | invalid_authorization_specification | role ”…” does not exist (as FATAL, at login) | no -U, so psql used the operating-system user (root in a container), or POSTGRES_USER changed on an existing volume; over a password connection it shows as password authentication failed | role does not exist |
28P01 | invalid_password | password authentication failed for user ”…” | a wrong password, but also a role that does not exist or has no (valid SCRAM) password — only the server log tells which; on Docker, a POSTGRES_PASSWORD changed on an existing volume | password authentication failed |
2BP01 | dependent_objects_still_exist | cannot drop table … because other objects depend on it | a view, foreign key, function or policy on another object that refers to it | cannot drop … depend on it |
40001 | serialization_failure | could not serialize access due to concurrent update (or … read/write dependencies, under SERIALIZABLE) | REPEATABLE READ or SERIALIZABLE and a row changed by another transaction | could not serialize access |
40001 | serialization_failure | canceling statement due to conflict with recovery | a long query on a read replica while the primary cleaned up rows | conflict with recovery |
40P01 | deadlock_detected | deadlock detected | two transactions locking the same rows in a different order | deadlock detected |
42501 | insufficient_privilege | permission denied for table … | a table created after the GRANT … ON ALL TABLES, or default privileges set for the wrong role | permission denied for table |
42501 | insufficient_privilege | permission denied for schema public | PostgreSQL 15 and later: no CREATE on public for ordinary roles | permission denied for schema public |
42501 | insufficient_privilege | permission denied for sequence … | INSERT granted on a serial table, but not USAGE on its sequence | permission denied for sequence |
42501 | insufficient_privilege | permission denied to create extension ”…” | the extension is not marked trusted, or the role lacks CREATE on the database | create extension |
42501 | insufficient_privilege | must be owner of table … | ALTER, DROP or CREATE INDEX by a role that only has grants | must be owner of table |
42501 | insufficient_privilege | new row violates row-level security policy for table … | the row fails the policy’s WITH CHECK, or an upsert also needs a SELECT policy | new row violates |
42501 | insufficient_privilege | query would be affected by row-level security policy for table … | pg_dump (which sets row_security = off) run as a role subject to row-level security | debugging row-level security |
42P01 | undefined_table | relation ”…” does not exist | capital letters, a schema not on the search path, or no USAGE on the schema | relation does not exist |
42703 | undefined_column | column ”…” does not exist | a column created with capitals, or a string in double quotes | column does not exist |
42704 | undefined_object | unrecognized configuration parameter ”…” | the name tells which of five: a newer dump (transaction_timeout), a removed setting, a MySQL or psql command, or a custom setting read before it was set | unrecognized configuration parameter |
42710 | duplicate_object | policy ”…” for table ”…” already exists | a migration that runs CREATE POLICY twice | CREATE POLICY IF NOT EXISTS |
42803 | grouping_error | column ”…” must appear in the GROUP BY clause or be used in an aggregate function | a selected column that is neither grouped nor aggregated, or grouped by a unique column or foreign key instead of the primary key | must appear in the GROUP BY clause |
42804 | datatype_mismatch | column ”…” is of type jsonb but expression is of type character varying (or text) | a typed text or varchar parameter, such as JDBC setString, where a literal would have worked | column is of type … but expression is of type |
42883 | undefined_function | operator does not exist: uuid = text | current_setting() or a text value compared with a uuid column | uuid = text |
42P10 | invalid_column_reference | there is no unique or exclusion constraint matching the ON CONFLICT specification | the conflict target does not match the index, often per tenant or partial | ON CONFLICT |
42P17 | invalid_object_definition | infinite recursion detected in policy for relation ”…” | a policy that queries its own table | infinite recursion |
428C9 | generated_always | cannot insert a non-DEFAULT value into column “id” | an explicit id (or NULL) for a generated always as identity column | null value in column “id” |
53200 | out_of_memory | out of shared memory (hint: max_locks_per_transaction) | one transaction locking thousands of tables or partitions | out of shared memory |
53300 | too_many_connections | remaining connection slots are reserved for … | too many application connections, connections nobody closes, or sessions idle in transaction — also as sorry, too many clients already | remaining connection slots |
54001 | statement_too_complex | stack depth limit exceeded | a trigger, function or policy that calls itself | stack depth limit exceeded |
55006 | object_in_use | database ”…” is being accessed by other users (or source database ”…”, for CREATE DATABASE … TEMPLATE) | a pool, an idle client or a GUI still connected to the database or its template | database is being accessed by other users |
55P03 | lock_not_available | canceling statement due to lock timeout (or could not obtain lock on row, with NOWAIT) | a migration or update waiting longer than lock_timeout | lock timeout |
57014 | query_canceled | canceling statement due to statement timeout (or … due to user request) | statement_timeout reached, waiting for a lock or working; user request is pg_cancel_backend() or a driver’s query timeout | statement timeout |
57P01 | admin_shutdown | terminating connection due to administrator command | pg_terminate_backend() on the session, for example on one idle in transaction | idle in transaction |
Several codes come back in more than one form; two matter for error handling. 40001 is both a serialization failure on the primary and a query cancelled on a replica: both mean “run the transaction again”, but the replica’s version can arrive as a dropped connection. And 42501 covers every missing privilege as well as row-level security refusing a row, so a retry loop or an error handler that branches on the code alone cannot tell “grant missing” from “policy refused this row” — read the message too.
Which class is the error in?
40— transaction rollback (40001,40P01)Retry the whole transactionFrom its start, not the statement; a savepoint retry deadlocked again in our test.25P02— current transaction is abortedLook for the error before itThe real cause came earlier in the same transaction; roll back. (25P03,25P04: the connection is gone — reconnect, and find the code that keeps transactions open.)42— syntax or access (42501,42P01,42703…)Usually a fix in the code, schema or grantsIf a retry does succeed —42P01after another session's commit, or on a different pooled connection — the cause is session state.22or23— data or integrityFix the data or the statementExcept23505on a_pkeyafter an import: fix the sequence.53,54,55— resources and object stateDepends on the code53200: raisemax_locks_per_transactionand restart.53300: find what holds the connections.54001: find the loop; a higher limit only moved it.55P03: keeplock_timeoutshort and retry the whole transaction.
Class meanings from PostgreSQL's error code list; the advice per class from the measurements in the articles linked above.
How do you read the SQLSTATE of an error?
In psql, \set VERBOSITY verbose prints it in front of the message (ERROR: 42501: permission denied for table invoices). psycopg2 puts it in e.pgcode and psycopg 3 in e.sqlstate (e.diag.sqlstate works in both) and map most codes to an exception class: UniqueViolation for 23505, InFailedSqlTransaction for 25P02, InvalidColumnReference for 42P10, StringDataRightTruncation for 22001. In the server log it is not shown by default; add %e to log_line_prefix. A message that comes before any server has answered, such as Connection refused … Is the server running on that host, has no server SQLSTATE at all; it is libpq’s, see connection refused.
What we did not measure
Connection errors in class 08 (08006 connection failure and friends), authentication errors in class 28, and errors from extensions. The table only lists errors we reproduced ourselves; PostgreSQL defines several hundred codes.
Sources: PostgreSQL 17 documentation, Appendix A, PostgreSQL error codes, read on 22 October 2026. The causes and SQLSTATEs in the table are from our own measurements, linked per row; 42P17, 42501 for query would be affected and 25P01 were re-checked, and 57014, 25P04, 57P01, 428C9, 0A000 and 25006 confirmed in the linked articles, on PostgreSQL 17.11 on 22 October 2026.