PostgreSQL Error Codes: What They Actually Mean

PostgreSQL error messages are surprisingly good — if you know where to look. Most developers see ERROR: relation "users" does not exist, read the message, fix the obvious thing, and move on. They never check the SQLSTATE code sitting right below the message. That code tells you the category of problem, which matters when you're writing error handling code that needs to distinguish a permission error from a missing table from a constraint violation.

How SQLSTATE codes work

Every PostgreSQL error has a 5-character SQLSTATE code. The first two characters are the class; the last three are the specific error within that class.

• 42 — syntax error or access rule violation
• 23 — integrity constraint violation
• 08 — connection exception
• 40 — transaction rollback
• 53 — insufficient resources

So 42501 is class 42 (access rule violation) + 501 (insufficient privilege). Once you know the class system, you can guess the category of an unfamiliar code before looking it up.

In psql, errors show up like:

ERROR:  permission denied for table orders
DETAIL:  ...
SQLSTATE: 42501

Connection problems

08001 — could not connect to server. The server isn't running, isn't on that port, or is blocking connections from that host. Check pg_hba.conf for host-based access rules.

08006 — connection dropped. Often a network issue, a server crash, or the connection was idle long enough that a firewall killed it. Your connection pool should handle reconnection.

08003 — connection does not exist. You tried to use a connection that was already closed. Happens when you use a connection after catching an error without properly handling the connection state.

Permission denied

42501 — insufficient privilege.

ERROR:  permission denied for table orders
SQLSTATE: 42501

Check what's granted:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'orders';

Missing things

42P01 — undefined table.

ERROR:  relation "orders" does not exist
SQLSTATE: 42P01

Either you're in the wrong schema, the table wasn't created, or there's a typo. Find it:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE '%order%';

42703 — undefined column. PostgreSQL's hints are often correct — read the HINT line.

Constraint violations

These are the ones your application code must handle gracefully, not crash on.

23505 — unique violation.

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

The DETAIL line tells you exactly which column and value caused the conflict. Catch this and return a user-friendly message, not a 500 error.

23503 — foreign key violation. Either the referenced row doesn't exist, or you're deleting a parent row that has children.

23502 — not null violation. You tried to insert NULL into a NOT NULL column. Usually a bug in the application layer — something expected to provide a value didn't.

Lock and concurrency errors

40P01 — deadlock detected.

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 67890;
         blocked by process 67891.
HINT:  See server log for query details.
SQLSTATE: 40P01

PostgreSQL rolls back one transaction (the "victim") and gives it this error. Catch 40P01 and retry the transaction automatically. If you're seeing this frequently, your transactions are acquiring locks in inconsistent order — locking rows in a consistent order (e.g., always by ID ascending) prevents most deadlocks.

55P03 — lock not available. Happens with SELECT ... FOR UPDATE NOWAIT. Return a conflict response and let the client retry.

Data type mismatches

22P02 — invalid text representation.

ERROR:  invalid input syntax for type integer: "abc"
SQLSTATE: 22P02

Common when user input gets passed directly to a typed column without validation.

42804 — datatype mismatch. The column and value types don't match and PostgreSQL won't coerce them automatically. Add an explicit cast: $1::numeric.

Handling errors in application code

The wrong way — parsing error message strings:

if "duplicate key" in str(e):  # Don't do this
    return "email already taken"

This breaks when PostgreSQL changes its phrasing or you run in a different locale. Use the SQLSTATE code.

Python (psycopg2):

import psycopg2

try:
    cursor.execute(
        "INSERT INTO users (email, name) VALUES (%s, %s)",
        (email, name)
    )
    conn.commit()
except psycopg2.errors.UniqueViolation:
    conn.rollback()
    return {"error": "email_taken"}
except psycopg2.errors.NotNullViolation:
    conn.rollback()
    return {"error": "missing_required_field"}
except psycopg2.OperationalError:
    # handle connection failure / reconnect
    raise

Node.js (node-postgres):

try {
  await pool.query(
    'INSERT INTO users (email, name) VALUES ($1, $2)',
    [email, name]
  );
} catch (err) {
  if (err.code === '23505') {
    return res.status(409).json({ error: 'email_taken' });
  }
  if (err.code === '23502') {
    return res.status(400).json({ error: 'missing_required_field' });
  }
  throw err;
}

In node-postgres, the error object has a code property containing the SQLSTATE string. In psycopg2, the exception class name matches the error name exactly.

The full list is in the pgref.dev error code reference — 250+ codes with descriptions and causes. But handle the codes above and you'll catch 95% of production issues.