White Paper: Just-in-Time Database Access Best Practices

Postgres Error Reference

This page lists the common Postgres errors that you may encounter, organized by category for easier navigation.

Complete error list can be found in the Postgres official documentation.

Data Integrity & Constraint Errors

These errors occur when data violates database constraints such as unique keys, foreign keys, or check constraints.

  • ERROR 23505: Duplicate key value violates unique constraint

    ERROR: duplicate key value violates unique constraint "table_pkey"
DETAIL: Key (id)=(1) already exists.

  • ERROR 23503: Insert or update violates foreign key constraint

    ERROR: insert or update on table "child_table" violates foreign key constraint "fk_constraint_name"
DETAIL: Key (parent_id)=(100) is not present in table "parent_table".

  • ERROR 23514: Check constraint violation

    ERROR: new row for relation "users" violates check constraint "check_age_positive"
DETAIL: Failing row contains (1, John, -5).

Query Structure & Aggregation Errors

These errors occur due to incorrect SQL query structure, GROUP BY usage, data type issues, and aggregate function problems.

  • ERROR 42803: Aggregate function calls cannot be nested

    ERROR: aggregate function calls cannot be nested
LINE 1: SELECT MAX(COUNT(*)) FROM orders GROUP BY customer_id;

  • ERROR 42803: Column must appear in the GROUP BY clause or be used in an aggregate function

    ERROR: column "orders.customer_name" must appear in GROUP BY clause or be used in an aggregate function
LINE 1: SELECT customer_id, customer_name, COUNT(*) FROM orders GROUP BY customer_id;

  • ERROR 42804: Datatype mismatch

    ERROR: column "age" is of type integer but expression is of type text
HINT: You will need to rewrite or cast the expression.

  • ERROR 42P21: Collation mismatch

    ERROR: could not determine which collation to use for string comparison
HINT: Use the COLLATE clause to set the collation explicitly.

Schema & Object Management Errors

These errors relate to creating, dropping, or modifying database objects and their dependencies.

  • ERROR 42P07: Relation already exists

    ERROR: relation "users" already exists

  • ERROR 2B000: Cannot drop role because objects depend on it

    ERROR: cannot drop role "admin" because some objects depend on it
DETAIL: 3 objects in database "mydb" depend on role "admin"

  • ERROR 2BP01: Cannot drop schema because objects depend on it

    ERROR: cannot drop schema "public" because other objects depend on it
DETAIL: table users depends on schema public
HINT: Use DROP ... CASCADE to drop the dependent objects too.

  • ERROR 2BP01: Cannot drop constraint used by foreign key constraint

    ERROR: cannot drop constraint "users_pkey" on table "users" because constraint "orders_user_id_fkey" on table "orders" requires it
HINT: You can drop constraint "orders_user_id_fkey" on table "orders" instead.

Permission & Authentication Errors

These errors relate to user permissions, ownership, and access control issues.

  • ERROR 42501: Permission denied for table

    ERROR: permission denied for table users

  • ERROR 42501: Must be owner of table

    ERROR: must be owner of table users

Concurrency & Locking Errors

These errors occur in multi-user environments due to transaction conflicts, deadlocks, and locking issues.

  • ERROR 40P01: Deadlock detected

    ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 5432.
Process 5432 waits for ShareLock on transaction 8765; blocked by process 1234.
HINT: See server log for query details.

  • ERROR 55P03: Lock not available

    ERROR: could not obtain lock on relation "orders"
DETAIL: Lock request timed out after 60000 milliseconds.
HINT: Close other transactions occupying the relation before retrying.

Connection & Resource Errors

These errors are related to server configuration limits, resource constraints, and connection management.

  • ERROR 53300: Too many connections

    ERROR: sorry, too many clients already
DETAIL: All connection slots are currently in use.
HINT: Consider increasing max_connections (currently 100).

Backup & Restore Tool Errors

These errors occur when using PostgreSQL backup and restore utilities like pg_dump and pg_restore.

Edit this page on GitHub