●PostgreSQL reference
PostgreSQL errors, explained and fixed
Every common PostgreSQL error, explained and fixed. What it means, why it happens, and the exact SQL to resolve it.
Connection and authentication
28000role "..." does not existThe PostgreSQL "role does not exist" error means you are connecting as a user Postgres has no record of. Here is why it happens and how to fix it.28P01password authentication failed for userPostgreSQL "password authentication failed for user" usually means a wrong password or a password-hashing mismatch. Here is how to diagnose and fix it.53300sorry, too many clients alreadyPostgreSQL "sorry, too many clients already" means you hit max_connections. The real fix is usually connection pooling, not just raising the limit.3D000database "..." does not existPostgreSQL "database does not exist" means the database you tried to connect to was never created or is misnamed. Create it or fix the connection string.53300too many connections for rolePostgreSQL "too many connections for role" means a per-role CONNECTION LIMIT was hit. Raise the limit or pool connections. Here is how.28000no pg_hba.conf entry for hostPostgreSQL "no pg_hba.conf entry for host" means no access rule matches your client IP, user, and database. Add a rule and reload, or connect with SSL.53300remaining connection slots are reservedPostgreSQL "remaining connection slots are reserved" means you hit the connection ceiling and only superuser slots remain. Pool connections or raise max_connections.08006could not connect to server, connection refusedPostgreSQL connection refused means nothing accepted the TCP connection. The server is down, on another port, or firewalled. Here is how to fix it.28000SSL connection is requiredPostgreSQL requiring an SSL connection means the server only accepts encrypted connections. Set sslmode=require or verify-full in your connection string.08006server closed the connection unexpectedlyPostgreSQL closing the connection unexpectedly usually means the backend crashed, was OOM-killed, or was restarted. Check the server log and add reconnect logic.25P03idle-in-transaction timeoutPostgreSQL idle-in-transaction timeout means a session held a transaction open while idle too long. Commit or roll back promptly, or raise the timeout.
Schema and identifiers
42P01relation "..." does not existThe PostgreSQL "relation does not exist" error means a table or view is not visible. Usually a search_path, case-sensitivity, or migration issue. Here is the fix.42703column "..." does not existPostgreSQL "column does not exist" is often caused by double quotes around a string literal. Here is that gotcha and the other common causes, with fixes.42704type "..." does not existPostgreSQL "type does not exist" usually means a custom type, enum, or extension type was never created or installed. Here is how to fix it.42883operator does not existPostgreSQL "operator does not exist" almost always means a type mismatch with no implicit cast. Cast one side, or install the extension that provides the operator.42883function does not existPostgreSQL "function does not exist" often means the arguments need a cast, or the function belongs to an extension. gen_random_uuid vs uuid-ossp explained.42702column reference is ambiguousPostgreSQL "column reference is ambiguous" means the column exists in more than one joined table. Qualify it with a table alias. Here is how.42725function is not uniquePostgreSQL function is not unique means several overloads match your call. Cast the arguments so exactly one fits, or schema-qualify the function.26000prepared statement does not existPostgreSQL prepared statement does not exist is usually caused by a transaction-mode pooler like PgBouncer. Disable server-side prepared statements or use session mode.
Constraints and writes
23505duplicate key value violates unique constraintPostgreSQL "duplicate key value violates unique constraint" means a row already exists with that value. Use ON CONFLICT upsert, or resync your sequence.23503insert or update violates foreign key constraintPostgreSQL "violates foreign key constraint" means a referenced parent row is missing. Insert the parent first, or fix the reference. Here is how.23502null value violates not-null constraintPostgreSQL "null value violates not-null constraint" means a required column got no value. Provide it, add a DEFAULT, or backfill before adding the constraint.23514new row violates check constraintPostgreSQL "violates check constraint" means a value failed a CHECK rule on the table. Correct the value, or review and adjust the constraint. Here is how.40P01deadlock detectedPostgreSQL "deadlock detected" means two transactions each hold a lock the other needs. Acquire locks in a consistent order and retry. Here is how.25P02current transaction is abortedPostgreSQL "current transaction is aborted" means an earlier statement failed and the rest are ignored until you roll back. Find the first error, then retry.23503update or delete violates a foreign key on another tablePostgreSQL update or delete violating a foreign key on another table means child rows still reference the parent. Delete children first, or use ON DELETE CASCADE.40001could not serialize access due to concurrent updatePostgreSQL could not serialize access is a serialization failure at REPEATABLE READ or SERIALIZABLE. It is by design: retry the transaction. Here is how.23P01conflicting key value violates exclusion constraintPostgreSQL violating an exclusion constraint means a row conflicts with an existing one, typically an overlapping range like a double-booked slot. Here is the fix.
Types and queries
22P02invalid input syntax for type integerPostgreSQL "invalid input syntax for type integer" means a non-numeric string reached an integer column, often an empty string. Here is how to fix it.42803column must appear in the GROUP BY clausePostgreSQL "must appear in the GROUP BY clause" means you selected a non-aggregated column alongside an aggregate. Group it, aggregate it, or use DISTINCT ON.42601syntax error at or nearPostgreSQL "syntax error at or near" points at the exact token it choked on. Often a reserved keyword used as an identifier, or MySQL-style syntax. Here is how to read it.42846cannot cast typePostgreSQL "cannot cast type" means no cast exists between those two types. Route through text, use a conversion function, or fix the column type.22003integer out of rangePostgreSQL "integer out of range" often means an int4 primary key ran past 2.1 billion. Migrate to bigint, or cast before arithmetic overflows.42883operator does not exist (type mismatch)PostgreSQL "operator does not exist: integer = text" is a type mismatch in a comparison. Cast one side to match the other. Here is how.22P02invalid input syntax for type uuidPostgreSQL invalid input syntax for type uuid means a value is not a valid UUID, often an empty string. Validate UUIDs and convert empty strings to NULL.22P02invalid input syntax for type jsonPostgreSQL invalid input syntax for type json means the value is not valid JSON, often from single quotes or trailing commas. Serialize JSON properly and bind as jsonb.22007invalid input syntax for type timestampPostgreSQL invalid input syntax for type timestamp means a value is not a recognizable date or time. Use ISO 8601, or parse with to_timestamp.22012division by zeroPostgreSQL division by zero means an expression divided by zero. Guard the divisor with NULLIF so a zero becomes NULL instead of an error. Here is how.22001value too long for type character varying(n)PostgreSQL value too long for character varying means a string exceeded a varchar(n) limit. Widen the column, switch to text, or truncate the input.
Operations and performance
42501permission denied for tablePostgreSQL "permission denied for table" means your role lacks the right privilege. GRANT it, and use ALTER DEFAULT PRIVILEGES so future tables are covered.57014canceling statement due to statement timeoutPostgreSQL "canceling statement due to statement timeout" means a query ran past statement_timeout. Optimize the query or raise the timeout. Here is how.42501permission denied for schemaPostgreSQL permission denied for schema means your role lacks USAGE on the schema. Grant USAGE and CREATE. Postgres 15 changed public schema defaults.55P03canceling statement due to lock timeoutPostgreSQL canceling statement due to lock timeout means a statement waited too long for a lock. Find the blocker, or set lock_timeout for migrations. Here is how.53200out of shared memoryPostgreSQL out of shared memory usually means a transaction needed more locks than the lock table holds. Raise max_locks_per_transaction or split the work.53100no space left on devicePostgreSQL no space left on device means the data or WAL volume is full. Free space, fix bloat, or drop stalled replication slots. Here is how.42P07relation "..." already existsPostgreSQL relation already exists means a table or index name is taken, usually a migration run twice. Use IF NOT EXISTS or make migrations idempotent.42701column already existsPostgreSQL column already exists means you tried to add a column that is already there, usually a migration run twice. Use ADD COLUMN IF NOT EXISTS.
Stop wrestling
with your database.
Download FluentDB and run your first AI-assisted query in 60 seconds.