PostgreSQL Error Code (SQLSTATE) Lookup

Paste a five-character PostgreSQL SQLSTATE such as 23505 or 42P01, or just the error text your application logged - duplicate key value violates unique constraint, relation does not exist, deadlock detected, too many clients - and this tool identifies the condition, explains why PostgreSQL raised it, and gives a concrete fix with the SQL to run. It also lists the matching exception class or code for psycopg, JDBC, node-postgres, pgx, lib/pq and Npgsql, and generates ready-to-paste catch blocks for each of them. The lookup understands psql output, Java stack traces and Python tracebacks, and falls back to keyword matching when no code is present. Everything runs entirely in your browser: nothing you paste is sent anywhere.

SQLSTATE code or error message

Common codes:

Error explained

SQLSTATE 23505 — unique_violation
Class 23 — Integrity Constraint Violation
Class 23: a row violates a PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, CHECK or EXCLUDE constraint; only the statement is rolled back.

Typical message
  duplicate key value violates unique constraint "users_email_key"
  DETAIL:  Key (email)=(alice@example.com) already exists.

Why it happens
  The row you inserted or updated has the same value as an existing row in a PRIMARY KEY, UNIQUE constraint or unique index. Frequent real-world causes: two requests racing to create the same record, a sequence that fell behind after a data import (id already exists), or a case-insensitive duplicate the app did not catch.

How to fix
  Use an upsert so the conflict is handled in SQL:
  INSERT INTO users (email, name) VALUES ($1, $2)
  ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
  -- or ignore duplicates
  INSERT ... ON CONFLICT DO NOTHING;
  If the primary key sequence is behind after an import:
  SELECT setval('users_id_seq', (SELECT max(id) FROM users));

Driver exception names
  psycopg 3 / psycopg2 : psycopg.errors.UniqueViolation  /  psycopg2.errors.UniqueViolation
  JDBC (pgjdbc)        : org.postgresql.util.PSQLException with getSQLState() == "23505"
  Go pgx               : *pgconn.PgError with Code == "23505"
  Go lib/pq            : *pq.Error with Code == "23505" (Code.Name() == "unique_violation")
  Node.js pg           : err.code === '23505'
  .NET Npgsql          : PostgresException with SqlState == "23505"

Handle it in code

-- PL/pgSQL: catch by condition name (or SQLSTATE '23505')
BEGIN
  INSERT INTO users (email) VALUES ('alice@example.com');
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'caught SQLSTATE % : %', SQLSTATE, SQLERRM;
    -- WHEN SQLSTATE '23505' THEN ...   -- equivalent form
END;

# Python (psycopg 3; psycopg2 uses psycopg2.errors.UniqueViolation)
import psycopg
try:
    cur.execute(sql, params)
except psycopg.errors.UniqueViolation as e:
    print(e.sqlstate, e.diag.message_primary, e.diag.constraint_name)

// Java (JDBC / pgjdbc)
try {
    stmt.execute(sql);
} catch (SQLException e) {
    if ("23505".equals(e.getSQLState())) {
        // handle unique_violation
    }
    // e instanceof org.postgresql.util.PSQLException -> ((PSQLException) e).getServerErrorMessage()
}

// Node.js (pg / node-postgres)
try {
  await client.query(sql, params);
} catch (err) {
  if (err.code === '23505') {
    // handle unique_violation: err.detail, err.constraint, err.table
  }
}

// Go (pgx v5)
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == "23505" {
    // handle unique_violation: pgErr.Detail, pgErr.ConstraintName
}

Do more than postgresql error code (sqlstate) lookup — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

PostgreSQL SQLSTATE reference table (129 codes by class)

Every PostgreSQL error carries a five-character SQLSTATE. The first two characters are the class (23 = integrity constraint violation, 42 = syntax or access rule, 08 = connection, 53 = resources, XX = internal) and the last three identify the specific condition. Codes ending in 000 are the generic condition for their class; codes containing P are PostgreSQL-specific extensions to the SQL standard. The condition names in the second column are what you use in PL/pgSQL EXCEPTION WHEN clauses, and psycopg derives its exception class names from them (unique_violation becomes psycopg.errors.UniqueViolation). Click any code to load it into the tool above.

Class 20 — Case Not Found

a PL/pgSQL CASE statement had no matching WHEN branch and no ELSE.

SQLSTATE	Condition name	Meaning
	case_not_found	PL/pgSQL CASE statement without matching WHEN / ELSE.

Class 21 — Cardinality Violation

a query that must return exactly one row returned several.

SQLSTATE	Condition name	Meaning
	cardinality_violation	Subquery used as a scalar returned more than one row.

Class 22 — Data Exception

a value cannot be represented in the target type - bad input syntax, overflow, truncation, division by zero.

SQLSTATE	Condition name	Meaning
	data_exception	Generic data conversion problem.
	string_data_right_truncation	Value longer than the column's varchar(n)/char(n).
	numeric_value_out_of_range	Number does not fit the target type / precision.
	null_value_not_allowed	NULL passed where a value is required.
	invalid_datetime_format	String is not a valid date/time literal.
	datetime_field_overflow	Date/time field value out of range (e.g. Feb 30).
	division_by_zero	Division or modulo by zero.
	character_not_in_repertoire	Byte sequence invalid for the database encoding.
	invalid_parameter_value	Argument value not accepted by the function.
	invalid_escape_sequence	Bad LIKE / string escape.
	string_data_length_mismatch	Bit string length does not match bit(n).
	invalid_regular_expression	Malformed regular expression.
	array_subscript_error	Array index or dimension problem.
	invalid_text_representation	String cannot be parsed as the target type (int, uuid, json...).
	invalid_binary_representation	Bad binary-format bind parameter.
	bad_copy_file_format	COPY input has wrong number of columns / bad format.
	untranslatable_character	Character has no equivalent in the client encoding.

Class 23 — Integrity Constraint Violation

a row violates a PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, CHECK or EXCLUDE constraint; only the statement is rolled back.

SQLSTATE	Condition name	Meaning
	integrity_constraint_violation	Generic constraint violation.
	restrict_violation	ON DELETE/UPDATE RESTRICT foreign key blocked the change.
	not_null_violation	NULL inserted into a NOT NULL column.
	foreign_key_violation	Row references a parent that does not exist, or parent still referenced.
	unique_violation	Duplicate value for a PRIMARY KEY or UNIQUE constraint/index.
	check_violation	Row fails a CHECK constraint or domain check.
	exclusion_violation	Row conflicts with an EXCLUDE constraint (e.g. overlapping ranges).

Class 24 — Invalid Cursor State

a cursor was used while not positioned on a row or after it was closed.

SQLSTATE	Condition name	Meaning
	invalid_cursor_state	Cursor not positioned on a row / already closed.

Class 25 — Invalid Transaction State

the command is not allowed in the current transaction state (aborted, read-only, inside a transaction block, etc.).

SQLSTATE	Condition name	Meaning
	invalid_transaction_state	Command not allowed in the current transaction state.
	active_sql_transaction	Command cannot run inside a transaction block.
	read_only_sql_transaction	Write attempted on a read-only transaction or hot standby.
	no_active_sql_transaction	SAVEPOINT / COMMIT without an open transaction.
	in_failed_sql_transaction	Transaction is aborted; every command is ignored until ROLLBACK.
	idle_in_transaction_session_timeout	Session sat idle inside a transaction too long.

Class 26 — Invalid SQL Statement Name

a prepared statement name is unknown to this connection - classic with connection poolers.

SQLSTATE	Condition name	Meaning
	invalid_sql_statement_name	Prepared statement not found (pooler / reconnect).

Class 27 — Triggered Data Change Violation

a trigger tried to modify a row that the same command is already modifying.

SQLSTATE	Condition name	Meaning
	triggered_data_change_violation	Trigger modified a row the same command is changing.

Class 28 — Invalid Authorization Specification

authentication failed - wrong password, unknown role or no matching pg_hba.conf rule.

SQLSTATE	Condition name	Meaning
	invalid_authorization_specification	Authentication rejected: no pg_hba rule or unknown role.
	invalid_password	Wrong password for the role.

Class 34 — Invalid Cursor Name

the named cursor or portal does not exist on this connection.

SQLSTATE	Condition name	Meaning
	invalid_cursor_name	Named cursor / portal does not exist.

Class 38 — External Routine Exception

an external-language (C, PL/Python, PL/Perl...) function did something it is not allowed to do.

SQLSTATE	Condition name	Meaning
	external_routine_exception	External-language function misbehaved.

Class 39 — External Routine Invocation Exception

an external function or trigger returned something PostgreSQL did not expect.

SQLSTATE	Condition name	Meaning
	trigger_protocol_violated	Trigger function returned something invalid.

Class 40 — Transaction Rollback

the whole transaction was rolled back by the server (deadlock, serialization failure) - retry it.

SQLSTATE	Condition name	Meaning
	transaction_rollback	Transaction rolled back by the server (generic).
	serialization_failure	Transaction conflicts under REPEATABLE READ / SERIALIZABLE; retry.
	statement_completion_unknown	Connection lost after sending a statement - unknown whether it committed.
	deadlock_detected	Two transactions wait on each other's locks; one is aborted.

Class 42 — Syntax Error or Access Rule Violation

the SQL text is wrong, references an unknown object, or the role lacks the privilege.

SQLSTATE	Condition name	Meaning
	syntax_error_or_access_rule_violation	Generic syntax / access rule problem.
	insufficient_privilege	Role lacks the privilege (permission denied).
	syntax_error	SQL text could not be parsed.
	duplicate_column	Column already exists on the table.
	ambiguous_column	Column name exists in more than one table of the query.
	undefined_column	Column does not exist (typo, case, wrong table).
	undefined_object	Type, role, extension, constraint or other object does not exist.
	duplicate_object	Object (role, type, index, extension...) already exists.
	ambiguous_function	Several function overloads match the call.
	grouping_error	Column must appear in GROUP BY or an aggregate.
	datatype_mismatch	Expression type does not match the target column / RETURN type.
	wrong_object_type	Command applied to the wrong kind of object (view vs table, index...).
	cannot_coerce	No cast exists between the two types.
	undefined_function	No function/operator with matching name and argument types.
	reserved_name	Name is reserved (pg_ prefix).
	undefined_table	Table / view / relation does not exist.
	undefined_parameter	$n placeholder without a bound value.
	duplicate_prepared_statement	Prepared statement name already in use on the backend.
	duplicate_table	Table / relation already exists.
	ambiguous_parameter	Same $n used with conflicting types.
	invalid_column_reference	ON CONFLICT target has no matching unique index, or bad column reference.
	invalid_table_definition	CREATE / ALTER TABLE definition is invalid.
	indeterminate_datatype	Type of a parameter or NULL literal cannot be inferred.

Class 44 — WITH CHECK OPTION Violation

a row written through a view would not be visible through that view.

SQLSTATE	Condition name	Meaning
	with_check_option_violation	Row written through a view would not be visible in it.

Class 53 — Insufficient Resources

the server ran out of memory, disk, connections or a configured limit.

SQLSTATE	Condition name	Meaning
	insufficient_resources	Server out of some resource (generic).
	disk_full	No space left on device.
	out_of_memory	Backend or shared memory allocation failed.
	too_many_connections	max_connections reached.
	configuration_limit_exceeded	A configured limit such as temp_file_limit was exceeded.

Class 54 — Program Limit Exceeded

a hard PostgreSQL limit was hit (stack depth, argument count, index row size).

SQLSTATE	Condition name	Meaning
	program_limit_exceeded	A hard PostgreSQL size limit was hit (index row, jsonb...).
	statement_too_complex	Stack depth limit exceeded (deep recursion / expression).
	too_many_columns	Table would exceed 1600 columns.
	too_many_arguments	Function call with more than 100 arguments.

Class 55 — Object Not In Prerequisite State

the object exists but is not in the right state for this operation (locked, in use, not yet committed).

SQLSTATE	Condition name	Meaning
	object_not_in_prerequisite_state	Object exists but is in the wrong state for the operation.
	object_in_use	Object is being used by other sessions (DROP DATABASE etc.).
	cant_change_runtime_param	Setting cannot be changed now / needs a restart.
	lock_not_available	NOWAIT / lock_timeout could not obtain the lock.
	unsafe_new_enum_value_usage	New enum value used before its transaction committed.

Class 57 — Operator Intervention

an administrator, a timeout or a server shutdown interrupted the session.

SQLSTATE	Condition name	Meaning
	operator_intervention	Session interrupted by an operator / shutdown (generic).
	query_canceled	Statement cancelled: statement_timeout or pg_cancel_backend.
	admin_shutdown	Server shutting down or backend terminated by admin.
	crash_shutdown	Another backend crashed; all sessions were reset.
	cannot_connect_now	Server is starting up, shutting down or in recovery.
	idle_session_timeout	Session idle (outside a transaction) longer than idle_session_timeout.

Class 58 — System Error

the operating system reported an error (file missing, I/O failure) - errors external to PostgreSQL itself.

SQLSTATE	Condition name	Meaning
	system_error	Operating-system level error (generic).
	io_error	Read/write to a data file failed.
	undefined_file	A file the server needs is missing (extension, tablespace, data file).
	duplicate_file	A file the server wanted to create already exists.

Class 72 — Snapshot Failure

old_snapshot_threshold pruned data the long-running query still needed.

SQLSTATE	Condition name	Meaning
	snapshot_too_old	old_snapshot_threshold pruned data the query needed.

Class 00 — Successful Completion

not an error - the statement completed normally.

SQLSTATE	Condition name	Meaning
	successful_completion	Statement completed normally.

Class 01 — Warning

the statement succeeded but PostgreSQL wants you to know about something (truncation, deprecated feature, etc.).

SQLSTATE	Condition name	Meaning
	warning	Generic warning; statement still succeeded.

Class 02 — No Data

a completion condition, not an error - the statement produced no rows.

SQLSTATE	Condition name	Meaning
	no_data	Statement returned no rows (completion condition).

Class 03 — SQL Statement Not Yet Complete

the statement is still executing (mostly seen in asynchronous or pipelined drivers).

SQLSTATE	Condition name	Meaning
	sql_statement_not_yet_complete	Statement still executing (async / pipeline drivers).

Class 08 — Connection Exception

the network connection to the server could not be opened, was closed, or the wire protocol was violated.

SQLSTATE	Condition name	Meaning
	connection_exception	Generic connection problem.
	sqlclient_unable_to_establish_sqlconnection	Client could not open a connection (refused, timeout, DNS).
	connection_does_not_exist	Operation used a connection that is already closed.
	connection_failure	Established connection was lost mid-session.
	protocol_violation	Client and server disagree on the wire protocol.

Class 09 — Triggered Action Exception

an action fired by a trigger failed.

SQLSTATE	Condition name	Meaning
	triggered_action_exception	Action fired by a trigger failed.

Class 0A — Feature Not Supported

the syntax is valid SQL but this PostgreSQL version (or this object type) does not implement it.

SQLSTATE	Condition name	Meaning
	feature_not_supported	Valid SQL that this PostgreSQL version does not implement.

Class 0B — Invalid Transaction Initiation

a transaction was started in a context where that is not allowed.

SQLSTATE	Condition name	Meaning
	invalid_transaction_initiation	Transaction started where not allowed.

Class 0F — Locator Exception

an invalid large-object / locator reference was used.

SQLSTATE	Condition name	Meaning
	locator_exception	Invalid large-object locator.

Class 0L — Invalid Grantor

GRANT / REVOKE was attempted by a role that cannot grant that privilege.

SQLSTATE	Condition name	Meaning
	invalid_grantor	Grantor cannot grant this privilege.

Class 0P — Invalid Role Specification

a role name is not valid in this context.

SQLSTATE	Condition name	Meaning
	invalid_role_specification	Role name not valid in this context.

Class 0Z — Diagnostics Exception

GET DIAGNOSTICS / GET STACKED DIAGNOSTICS was used incorrectly.

SQLSTATE	Condition name	Meaning
	diagnostics_exception	GET DIAGNOSTICS misuse.

Class 2B — Dependent Privilege Descriptors Still Exist

an object cannot be dropped because other objects or privileges depend on it.

SQLSTATE	Condition name	Meaning
	dependent_objects_still_exist	DROP blocked by dependent views, FKs, functions.

Class 2D — Invalid Transaction Termination

COMMIT / ROLLBACK was issued where transaction control is not allowed (functions, sub-transactions).

SQLSTATE	Condition name	Meaning
	invalid_transaction_termination	COMMIT/ROLLBACK inside a function or sub-transaction.

Class 2F — SQL Routine Exception

a SQL-language or PL/pgSQL function misbehaved (missing RETURN, forbidden statement).

SQLSTATE	Condition name	Meaning
	sql_routine_exception	SQL-language / PL function violated its declaration.
	function_executed_no_return_statement	PL/pgSQL function ended without RETURN.

Class 3B — Savepoint Exception

a savepoint name is invalid or unknown.

SQLSTATE	Condition name	Meaning
	savepoint_exception	Savepoint problem.
	invalid_savepoint_specification	ROLLBACK TO / RELEASE a savepoint that does not exist.

Class 3D — Invalid Catalog Name

the database named in the connection string does not exist.

SQLSTATE	Condition name	Meaning
	invalid_catalog_name	Database named in the connection does not exist.

Class 3F — Invalid Schema Name

the schema does not exist or no schema was selected for CREATE.

SQLSTATE	Condition name	Meaning
	invalid_schema_name	Schema does not exist / no schema selected for CREATE.

Class F0 — Configuration File Error

postgresql.conf / pg_hba.conf could not be read or contains errors.

SQLSTATE	Condition name	Meaning
	config_file_error	postgresql.conf / pg_hba.conf has errors.
	lock_file_exists	postmaster.pid exists - another server may be running.

Class HV — Foreign Data Wrapper Error (SQL/MED)

a foreign data wrapper such as postgres_fdw or file_fdw failed.

SQLSTATE	Condition name	Meaning
	fdw_error	Generic foreign data wrapper error.
	fdw_unable_to_establish_connection	postgres_fdw could not connect to the remote server.

Class P0 — PL/pgSQL Error

raised from PL/pgSQL code - RAISE EXCEPTION, STRICT SELECT INTO, ASSERT.

SQLSTATE	Condition name	Meaning
	plpgsql_error	Generic PL/pgSQL error.
	raise_exception	Custom RAISE EXCEPTION from PL/pgSQL.
	no_data_found	SELECT ... INTO STRICT returned no rows.
	too_many_rows	SELECT ... INTO STRICT returned more than one row.
	assert_failure	PL/pgSQL ASSERT condition was false.

Class XX — Internal Error

a bug or data corruption inside PostgreSQL - check the server log and run pg_amcheck.

SQLSTATE	Condition name	Meaning
	internal_error	Unexpected internal error (bug, corruption, extension crash).
	data_corrupted	Data file page is corrupt.
	index_corrupted	Index structure is corrupt; REINDEX.

How to find the SQLSTATE of a PostgreSQL error

psql: run \set VERBOSITY verbose and every error prints an extra SQLSTATE: line; \errverbose re-prints the last error in full.
Server log: set log_error_verbosity = verbose, or use log_line_prefix = '%e' to include the SQLSTATE in every log line.
Python: e.sqlstate (psycopg 3) or e.pgcode (psycopg2); the exception class itself already names the condition.
Java: SQLException.getSQLState(); the PSQLException message also shows the server message and, with getServerErrorMessage(), the DETAIL / HINT fields.
Node.js: err.code, plus err.detail, err.constraint, err.table, err.column.
Go: pgconn.PgError.Code (pgx) or pq.Error.Code (lib/pq), with Code.Name() returning the condition name.
PL/pgSQL: inside an EXCEPTION block the variables SQLSTATE and SQLERRM are set, and GET STACKED DIAGNOSTICS exposes DETAIL, HINT, constraint and table names.

How to use

Paste a SQLSTATE code (23505, 42P01, 57014...) or the error message itself - including psql output, a psycopg2.errors.UniqueViolation traceback line or a PSQLException message - into the input box, or click one of the common codes.
Read the report: condition name, class meaning, the typical server message, why it happens, and the fix with SQL or configuration commands. For free-text input the best match is shown first with up to four alternatives.
Copy the 'Handle it in code' snippet to catch exactly that condition in PL/pgSQL, Python, Java, Node.js or Go, and use the reference table below to browse every class.

Frequently asked questions

What is a PostgreSQL SQLSTATE code and how is it different from the error message?

SQLSTATE is a five-character code defined by the SQL standard (with PostgreSQL-specific additions containing the letter P). The first two characters name the class - 23 for integrity constraint violations, 42 for syntax or privilege problems, 08 for connection failures, 40 for transaction rollbacks - and the last three identify the exact condition, for example 23505 unique_violation. Unlike the human-readable message, which changes between versions and translations and embeds table or constraint names, the SQLSTATE is stable, so application code should branch on it (err.code, getSQLState(), e.sqlstate) rather than on message text.

Which PostgreSQL errors should my application retry automatically?

Class 40 errors are designed to be retried: 40001 serialization_failure and 40P01 deadlock_detected mean the server already rolled the transaction back and a fresh attempt will usually succeed - retry the whole transaction with a short back-off. Connection-level failures (08006 connection_failure, 57P01 admin_shutdown, 57P03 cannot_connect_now) should be retried on a new connection. Do not blindly retry class 23 constraint violations, class 42 syntax or privilege errors or class 22 data errors: they will fail identically until the data or SQL is fixed. 40003 statement_completion_unknown needs an idempotency check first because the commit may have succeeded.

How do I see which statement produced a SQLSTATE and debug it against the database?

In psql, \set VERBOSITY verbose prints the SQLSTATE, and \errverbose re-shows the last error with DETAIL, HINT and the source location. In the server log, log_error_verbosity = verbose or %e in log_line_prefix records it for every failure. To see the failing statement, the SQLSTATE and the affected table side by side against a live database - and to have AI explain the error and propose the corrected SQL - use Chat2DB: download the desktop client at https://chat2db.ai/download or open the web app at https://app.chat2db.ai, run the statement, and inspect the constraint, index or permission the error refers to without leaving the editor.