PostgreSQL error codes (SQLSTATE): what they mean and how to fix the common ones

Fixing the wrong thing is the usual time-waster, so confirm the code first.

1. In psql, turn on verbose error output so the code is shown with each error.
   TextCopy

   \set VERBOSITY verbose

2. In application code, read the code from your driver's error object (sqlstate, code or pgcode, depending on the library).
3. Match the first two characters against the class table, then the full code against the condition list in the official PostgreSQL error code appendix. Use the page for your server version if you run an older release, since the appendix doesn't say which version introduced each code.

These are not data errors. They mean a transaction lost a conflict and can usually succeed if run again.

1. For 40001, retry the whole transaction, not just the failed statement. Include the logic that decides which SQL to run and which values to use, because the data it read may now be different. The PostgreSQL docs recommend retrying this error. A retry isn't guaranteed to succeed, so allow several attempts.
2. For 40P01, PostgreSQL aborts one of the deadlocked transactions and which one is hard to predict. Retrying the aborted transaction may be advisable.
3. To reduce deadlocks, take locks on multiple objects in the same order in every transaction, and ask for the most restrictive lock mode you need first. Two transactions updating the same rows in opposite order is enough to deadlock on row locks alone.
4. Keep transactions short and never leave one open while waiting for user input.

These point to the data or the statement, and the error message names the constraint or column involved.

1. 23505 (unique_violation): the key already exists. Check the data that is being inserted or updated against the existing rows.
2. 23503 (foreign_key_violation): the referenced row is missing, or you are inserting or deleting in the wrong order. Insert the parent row first, and delete child rows before parents.
3. 23502 (not_null_violation): a NULL was supplied for a NOT NULL column. Provide a value or fix the code that builds the row.
4. 22P02 (invalid_text_representation): a value can't be converted to the column's type, such as text where a number is expected. Check what your application sends.

Usually a naming, schema or permissions problem rather than a damaged database.

1. 42P01 (undefined_table) and 42703 (undefined_column): check the spelling, quoting and letter case of the name. Quoted names are case sensitive.
2. For a missing table, check that you are in the right schema and that search_path includes it, and that your migrations have actually run on this database.
3. 42501 (insufficient_privilege): the role lacks permission on the object. Ask the database owner or an administrator to grant what is needed.

An earlier statement in the same transaction failed, so PostgreSQL rejects everything after it until you roll back.

1. Find and fix the first error in the log, since 25P02 is only a follow-on.
2. Roll back the transaction (or to a savepoint) and run it again.
   SQLCopy

   ROLLBACK;

Both come from a statement being stopped before it finished, by a lock wait or a timeout or a cancel, not from bad data.

1. 55P03 (lock_not_available) means a statement could not get a lock it needed. Two common triggers: lock_timeout is set to a non-zero value and the wait ran past it (the setting is disabled by default, at 0, and applies to each lock attempt separately), or the query asked not to wait at all, for example SELECT ... FOR UPDATE NOWAIT, which raises an error if a row can't be locked immediately. Check the query for NOWAIT. For reference, a session reaches this state with a setting like the one below. This is an example of what produces the error, not a fix.
   SQLCopy

   SET lock_timeout = '2s';

2. Find the session that holds the lock. pg_blocking_pids() returns the process IDs blocking a given session, and pg_stat_activity shows what each one is running. Run this while the problem is happening.
   SQLCopy

   SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
   FROM pg_stat_activity
   WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. Once the blocker finishes, retry the statement. The lasting fixes are shorter transactions that release locks sooner, or a longer lock_timeout where waiting is acceptable. See the lock_timeout and statement_timeout reference. To look at pg_locks directly, use the same pid values.
4. 57014 (query_canceled) means the statement was cancelled. statement_timeout aborts any statement that runs longer than its limit (0, the default, disables it), and pg_cancel_backend(pid) cancels a session's current query on request while leaving the session connected. Find out which applies, then either optimise the query or raise the timeout where it is justified.

The server is refusing work because a limit has been reached.

1. 53300 (too_many_connections): the max_connections limit is reached. Look at pg_stat_activity to see which clients hold the connections, and reduce what your application opens. Raising max_connections is the last resort. The default is typically 100. Some slots (3 by default, set by superuser_reserved_connections) are kept so that superusers can still connect when the server is nearly full. See the connection settings reference.
2. 53100 (disk_full), 53200 (out_of_memory) and 53400 (configuration_limit_exceeded): these mean the server ran out of a resource or hit a configured limit. Read the full message and the server log to see which one, and involve the server's administrator or your hosting provider.

The server rejected the login before any query ran.

1. Check the role name and password the application is actually using, including values from environment variables or config files.
2. Ask the database administrator to check the matching record in pg_hba.conf. Records are read top to bottom, the first match is used, and if none matches access is denied. After an edit, the file is applied on a reload (SELECT pg_reload_conf();), see the pg_hba.conf reference.
3. Also check the password method, md5 or scram-sha-256. scram-sha-256 is the more secure option but isn't supported by older client libraries, and a password stored with the md5 setting of password_encryption can only be used with the md5 and password methods. If you switch methods, the docs say to confirm client library support first, then set password_encryption, have users set new passwords, and only then change pg_hba.conf. See the password authentication reference.

The session ended, so the application has to open a new one.

1. 08006 (connection_failure): check the network path, whether the server crashed or restarted, and any idle timeouts on firewalls or load balancers between you and the database.
2. 57P01 (admin_shutdown): the server was shut down or your session was ended by an administrator. pg_terminate_backend(pid) is the function that ends a session entirely, as opposed to pg_cancel_backend, which only cancels the current query. Reconnect once the server is back.
3. Make the application reconnect with a retry instead of failing permanently.

Testing the code or condition name is more reliable than reading message text.

1. Branch on the SQLSTATE or the condition name, never on message text. Unknown codes can fall back to handling by class.
2. In PL/pgSQL, catch a condition by name.
   SQLCopy

   EXCEPTION WHEN unique_violation THEN
     -- handle the duplicate here

3. Remember PL/pgSQL does not recognise condition names for the warning classes 00, 01 and 02.