/codes /postgresql/postgresql-sqlstate-error-codes

PostgreSQL SQLSTATE error codes: 23505, 23503, 42P01, 40001 and more

updated 2026-10-20

tool: PostgreSQL
versions: PostgreSQL 12 – 18
os: any

Every PostgreSQL error carries a five-character SQLSTATE code. The first two characters are the class (23 integrity constraint violation, 42 syntax or access rule violation, 40 transaction rollback). Handle errors by code, not by message: messages can be translated (lc_messages) and reworded between versions.

Codes applications handle

SQLSTATE	Name	Typical cause	Usual handling
23505	unique_violation	duplicate primary key or unique value	return "already exists", or upsert
23503	foreign_key_violation	referenced row missing, or still referenced on delete	validation error
23502	not_null_violation	NULL in a NOT NULL column	validation error
23514	check_violation	CHECK constraint failed	validation error
22P02	invalid_text_representation	'abc'::int, bad UUID text	validate input first
22012	division_by_zero	x / 0	fix the query, NULLIF(y, 0)
42P01	undefined_table	table missing or not on search_path	deployment or migration bug
42703	undefined_column	misspelled or missing column	deployment or migration bug
40001	serialization_failure	conflict under SERIALIZABLE or REPEATABLE READ	retry the whole transaction
40P01	deadlock_detected	two transactions wait for each other	retry; lock rows in a consistent order
55P03	lock_not_available	NOWAIT or lock_timeout reached	retry later
57014	query_canceled	statement_timeout or a cancel request	optimise, or raise the timeout
53300	too_many_connections	max_connections reached	use a connection pool

Real codes from PostgreSQL 18

bash

npm init -y >/dev/null
npm install --silent --no-audit --no-fund @electric-sql/pglite@0.5.10 >/dev/null 2>&1
cat > q.mjs <<'EOF'
import { PGlite } from "@electric-sql/pglite";
const db = new PGlite();
await db.exec(`create table users (id int primary key, email text not null unique check (email like '%@%'));
               create table orders (id int primary key, user_id int references users(id));
               insert into users values (1, 'a@example.com');`);
for (const sql of [
  `insert into users values (1, 'b@example.com')`,
  `insert into orders values (1, 99)`,
  `insert into users values (2, null)`,
  `insert into users values (3, 'no-at-sign')`,
  `select 'abc'::int`,
  `select 1 / 0`,
  `select * from missing_table`,
  `select nme from users`,
]) {
  try { await db.query(sql); } catch (e) { console.log(e.code.padEnd(6), e.message); }
}
EOF
node q.mjs

Output

23505  duplicate key value violates unique constraint "users_pkey"
23503  insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
23502  null value in column "email" of relation "users" violates not-null constraint
23514  new row for relation "users" violates check constraint "users_email_check"
22P02  invalid input syntax for type integer: "abc"
22012  division by zero
42P01  relation "missing_table" does not exist
42703  column "nme" does not exist

In drivers the code is a property of the error: err.code in node-postgres, e.sqlstate in psycopg 3, getSQLState() in JDBC.

Sources

errors · postgresqlPostgreSQL: duplicate key value violates unique constraintPostgreSQL raises duplicate key value violates unique constraint (SQLSTATE 23505) when a key repeats. Read the DETAIL line, then upsert or reset the sequence.errors · postgresqlPostgreSQL: ERROR: relation "users" does not existPostgreSQL reports relation does not exist (42P01) when a table is missing, in a schema outside the search_path, or was created with a quoted mixed-case name.commands · postgresqlPostgreSQL upsert: INSERT … ON CONFLICT DO UPDATE and MERGEInsert a row or update it if the key exists with INSERT ... ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col, skip duplicates with DO NOTHING, or use MERGE.

Codes applications handle

Real codes from PostgreSQL 18

Sources

Related pages