(opens in a new tab)

PostgreSQL Error Codes (SQLSTATE) Explained with Fixes

September 4, 2026 by Chat2DB Team

Every error PostgreSQL raises carries a five-character SQLSTATE code alongside the human-readable message. The message is what you read; the code is what your application should branch on. Messages change wording between versions and get translated by lc_messages; codes do not. If your retry logic matches on the string "deadlock detected", it will silently stop working on a server configured in German. If it matches on 40P01, it works everywhere.

This guide covers how SQLSTATE codes are structured, how to get at them from psql and the common client libraries, what each class means, and the specific codes you will run into in day-to-day work, with the exact message and the fix for each.

What a SQLSTATE is

A SQLSTATE is five characters: a two-character class followed by a three-character subclass. Characters are digits or upper-case letters. The class tells you the category of the problem; the subclass narrows it down.

2 3 5 0 5
└─┬─┘ └─┬─┘
 class  subclass
 23 = integrity constraint violation
 505 = unique_violation

A subclass of 000 means "the generic error for this class". So 23000 is an unspecified integrity constraint violation, while 23505 is specifically a unique violation. Codes whose subclass starts with a letter (42P01, 40P01, 25P02) are PostgreSQL-specific extensions to the SQL standard. Codes whose class starts with a letter that the standard does not use (P0, XX) are entirely PostgreSQL's own.

Each code also has a condition name such as unique_violation or undefined_table. These names are what you use in PL/pgSQL exception handlers and what client libraries like psycopg turn into exception class names. The full list lives in Appendix A of the PostgreSQL manual and in src/backend/utils/errcodes.txt in the source tree.

How to see the code

psql hides the SQLSTATE by default. Turn on verbose mode and it appears in front of the message, together with the source file and line where the error was raised:

psql -d appdb

\set VERBOSITY verbose
INSERT INTO users (email) VALUES ('a@b.com');

ERROR:  23505: duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(a@b.com) already exists.
SCHEMA NAME:  public
TABLE NAME:  users
CONSTRAINT NAME:  users_email_key
LOCATION:  _bt_check_unique, nbtinsert.c:666

If you have already hit an error in default mode, \errverbose reprints the last error with the verbose fields, so you do not need to re-run the statement.

In PL/pgSQL the code is available as the SQLSTATE variable inside an EXCEPTION block, and the rest of the diagnostics come from GET STACKED DIAGNOSTICS:

DO $$
DECLARE
  v_state     text;
  v_msg       text;
  v_detail    text;
  v_constraint text;
BEGIN
  INSERT INTO users (email) VALUES ('a@b.com');
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS
    v_state      = RETURNED_SQLSTATE,
    v_msg        = MESSAGE_TEXT,
    v_detail     = PG_EXCEPTION_DETAIL,
    v_constraint = CONSTRAINT_NAME;
  RAISE NOTICE 'state=% constraint=% msg=% detail=%',
    v_state, v_constraint, v_msg, v_detail;
END $$;

Client libraries expose the same field:

The pattern is the same everywhere: match on the code, log the message.

Chat2DB also has a free PostgreSQL error code lookup (opens in a new tab) that explains any SQLSTATE when you just want to paste one in and read what it means.

The class map

The first two characters are the fastest way to triage an unfamiliar code:

A useful rule of thumb: classes 22, 23 and 42 are bugs in the SQL or the data you sent and retrying will not help. Class 40 is designed to be retried. Classes 53, 57 and 08 are about the server or the connection, and the fix is usually operational rather than in the query.

The codes you will actually meet

23505 unique_violation

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(a@b.com) already exists.

A row with the same value already exists under a unique constraint or index. Either the data really is duplicated, the sequence is behind the data after a manual insert or restore, or two sessions raced. For an insert that should tolerate existing rows, use an upsert:

INSERT INTO users (email, name)
VALUES ('a@b.com', 'Ann')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

The causes and fixes are covered in depth in Fix "duplicate key value violates unique constraint".

23503 foreign_key_violation

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

Or, from the other side, when deleting a parent that still has children:

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(42) is still referenced from table "orders".

Insert the parent first, or decide the referential action once at schema time instead of handling it in every code path:

ALTER TABLE orders
  DROP CONSTRAINT orders_customer_id_fkey,
  ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

23502 not_null_violation

ERROR:  null value in column "email" of relation "users" violates not-null constraint
DETAIL:  Failing row contains (7, null, Ann).

Supply the value, add a DEFAULT, or drop the constraint if it was wrong:

ALTER TABLE users ALTER COLUMN email SET DEFAULT '';
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

The DETAIL line lists the whole failing row in column order, which is often faster than reading the INSERT.

23514 check_violation

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (12, Widget, -5.00).

Look up the constraint definition rather than guessing at it:

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass AND contype = 'c';

42P01 undefined_table

ERROR:  relation "users" does not exist
LINE 1: SELECT * FROM users;
                      ^

The table is misspelled, in a schema not on search_path, in a different database, or was created with a quoted mixed-case name. Check the last two:

SHOW search_path;
SELECT table_schema, table_name FROM information_schema.tables
WHERE table_name ILIKE 'users';

If the result shows "Users", the table was created quoted and must always be referenced as "Users".

42703 undefined_column

ERROR:  column "emial" does not exist
LINE 1: SELECT emial FROM users;
               ^
HINT:  Perhaps you meant to reference the column "users.email".

Same causes as above at the column level. A common variant is using double quotes where you meant a string literal: WHERE name = "Ann" is a column reference to Ann, not the text 'Ann'.

42P07 duplicate_table

ERROR:  relation "users" already exists

Usually a migration re-running. Make DDL idempotent with CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, and DROP ... IF EXISTS.

42601 syntax_error

ERROR:  syntax error at or near "FORM"
LINE 1: SELECT * FORM users;
                 ^

The caret marks the first token the parser could not accept, which is often one token after the real mistake. Trailing commas before FROM, a missing closing parenthesis, and reserved words used as identifiers (user, order, group) without quotes account for most of these.

42501 insufficient_privilege

ERROR:  permission denied for table users

Grant the privilege to the role, or to a group role the user belongs to. Remember that GRANT applies to existing objects only; use ALTER DEFAULT PRIVILEGES for tables created later:

GRANT SELECT, INSERT, UPDATE ON users TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_ro;

42883 undefined_function

ERROR:  function date_trunc(text, integer) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The function exists but not for those argument types, or an extension is not installed. The related operator does not exist: integer = text also carries 42883. Cast explicitly, or install the extension:

SELECT date_trunc('day', to_timestamp(1700000000));
CREATE EXTENSION IF NOT EXISTS pgcrypto;

22P02 invalid_text_representation

ERROR:  invalid input syntax for type integer: "abc"

A string that cannot be parsed as the target type, often from a parameter bound as text or a CSV column. Validate at the boundary, or test conversion without raising (PostgreSQL 16 and later):

SELECT pg_input_is_valid('abc', 'integer');   -- false

22001 string_data_right_truncation

ERROR:  value too long for type character varying(50)

PostgreSQL never silently truncates. Widen the column, or drop the length limit entirely, which costs nothing in PostgreSQL:

ALTER TABLE users ALTER COLUMN name TYPE text;

40001 serialization_failure

ERROR:  could not serialize access due to concurrent update

or under SERIALIZABLE:

ERROR:  could not serialize access due to read/write dependencies among transactions
HINT:  The transaction might succeed if retried.

Not a bug. The isolation level did its job and the transaction must be retried from the beginning. See the retry section below.

40P01 deadlock_detected

ERROR:  deadlock detected
DETAIL:  Process 5120 waits for ShareLock on transaction 9021; blocked by process 5133.
        Process 5133 waits for ShareLock on transaction 9020; blocked by process 5120.
HINT:  See server log for query details.

Two transactions took locks in opposite orders. Retry the victim, then fix the ordering. A full walkthrough is in How to fix ERROR: deadlock detected.

53300 too_many_connections

FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for non-replication superuser connections

Both messages share the code. Find who is holding the slots before raising max_connections:

SELECT usename, application_name, state, count(*)
FROM pg_stat_activity GROUP BY 1, 2, 3 ORDER BY 4 DESC;

Sessions sitting in idle in transaction are the usual culprit; see idle in transaction and the max_connections guide.

57014 query_canceled

ERROR:  canceling statement due to statement timeout
ERROR:  canceling statement due to user request

The first comes from statement_timeout, the second from pg_cancel_backend() or Ctrl-C in psql. Raise the timeout for the one session that needs it rather than globally:

SET statement_timeout = '5min';

statement_timeout in PostgreSQL covers where to set it and what value to use.

25P02 in_failed_sql_transaction

ERROR:  current transaction is aborted, commands ignored until end of transaction block

An earlier statement in this transaction failed and you kept sending commands. PostgreSQL will refuse everything until ROLLBACK. The real error is the previous one in your log. If you need to survive a failing statement mid-transaction, wrap it in a SAVEPOINT and ROLLBACK TO SAVEPOINT on failure.

28P01 invalid_password

FATAL:  password authentication failed for user "app"

Wrong password, or the role exists but pg_hba.conf matched a rule with a method the client cannot satisfy. Check which rule matched with pg_hba_file_rules and confirm the password encryption method (scram-sha-256 versus md5) agrees between server and client.

3D000 invalid_catalog_name

FATAL:  database "appdb" does not exist

The database name in the connection string is wrong, or the connection went to a different server than you thought. psql -l lists the databases actually present.

Catching codes in PL/pgSQL

Exception handlers accept either the condition name or the literal code:

CREATE OR REPLACE FUNCTION register_user(p_email text, p_name text)
RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE
  v_id bigint;
BEGIN
  INSERT INTO users (email, name) VALUES (p_email, p_name)
  RETURNING id INTO v_id;
  RETURN v_id;
EXCEPTION
  WHEN unique_violation THEN
    SELECT id INTO v_id FROM users WHERE email = p_email;
    RETURN v_id;
  WHEN SQLSTATE '23503' THEN
    RAISE EXCEPTION 'referenced row missing: %', SQLERRM
      USING ERRCODE = 'foreign_key_violation';
END $$;

Two things to know. A block with an EXCEPTION clause runs inside a subtransaction, which costs a savepoint per call; do not wrap every statement in one in a hot loop. And you can catch a whole class at once: a class-level condition such as WHEN integrity_constraint_violation (or its code, WHEN SQLSTATE '23000') matches every 23xxx error, not just the generic one.

When you raise your own errors, give them a code so callers can match on it:

RAISE EXCEPTION 'balance would go negative'
  USING ERRCODE = 'check_violation', HINT = 'top up first';

Retrying 40001 and 40P01

Serialization failures and deadlocks are the two codes PostgreSQL explicitly expects you to retry. The retry must restart the whole transaction, not just the failed statement: after either error the transaction is aborted, and any reads it did before the failure may be stale. Catching 40001 inside a PL/pgSQL function does not help for the same reason, since the outer transaction is what needs to restart.

import random
import time
 
import psycopg
from psycopg.errors import DeadlockDetected, SerializationFailure
 
 
def transfer(conn, src, dst, amount):
    with conn.transaction():
        conn.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (amount, src),
        )
        conn.execute(
            "UPDATE accounts SET balance = balance + %s WHERE id = %s",
            (amount, dst),
        )
 
 
def with_retry(fn, attempts=5):
    for attempt in range(1, attempts + 1):
        try:
            with psycopg.connect("dbname=appdb", autocommit=True) as conn:
                conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE
                return fn(conn)
        except (SerializationFailure, DeadlockDetected) as e:
            if attempt == attempts:
                raise
            delay = 0.05 * (2 ** attempt) + random.uniform(0, 0.05)
            print(f"attempt {attempt} failed with {e.sqlstate}, retrying in {delay:.2f}s")
            time.sleep(delay)
 
 
with_retry(lambda c: transfer(c, 1, 2, 100))

Exponential backoff with jitter matters: if every retrying client waits the same fixed interval, they collide again on the next attempt. Cap the attempt count, and make sure the work inside the transaction has no side effects outside the database (sending an email, calling an API) that would be repeated on retry.

When you are working through a set of failing statements interactively, a client that shows the SQLSTATE, DETAIL and HINT next to the result helps. Chat2DB surfaces the full error diagnostics for PostgreSQL and can explain them inline; download it at chat2db.ai/download (opens in a new tab) or use it in the browser at app.chat2db.ai (opens in a new tab).

Summary

SQLSTATE codes are the stable, language-independent identity of a PostgreSQL error: two characters of class, three of subclass. Turn on \set VERBOSITY verbose in psql, read SQLSTATE and GET STACKED DIAGNOSTICS in PL/pgSQL, and use e.sqlstate, err.code or getSQLState() in application code. Classes 22, 23 and 42 mean the SQL or data is wrong and should not be retried; class 40 should always be retried from the start of the transaction with backoff; classes 53, 57 and 08 point at the server or the connection rather than the query. Match on the code, log the message, and your error handling will survive both version upgrades and a server whose locale is not English.