Fix "relation does not exist" in PostgreSQL (42P01)
Chat2DB TeamERROR: relation "users" does not exist
LINE 1: SELECT id, email FROM users WHERE id = 42;
^This is one of the most common PostgreSQL errors, and it is almost never what it looks like. The table usually does exist — just not where PostgreSQL is looking. "Relation" is PostgreSQL's umbrella term for anything in pg_class: tables, views, materialized views, sequences, indexes and foreign tables all raise the same message. The SQLSTATE is 42P01 (undefined_table), and the LINE n: marker plus the caret point at the exact token the parser could not resolve.
Every cause below has the same shape: the name you typed does not resolve to an object in the current database, in a schema on the current search_path, with matching case, in the current session. Work through them in order; the first three account for most cases.
Start with the diagnostic query
Before guessing, ask the catalog where the object actually is. pg_class is per-database, so run this connected to the database you think you are using:
SELECT n.nspname AS schema,
c.relname AS relation,
c.relkind AS kind, -- r=table v=view m=matview S=sequence p=partitioned
pg_get_userbyid(c.relowner) AS owner
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ILIKE 'users'
ORDER BY 1, 2;Three possible outcomes:
- No rows. The relation is not in this database at all. Go to causes 1, 4 or 7.
- One row, but in a schema other than
public. Thesearch_pathdoes not include it. Cause 2. - One row with unexpected capitalisation (
Users). The identifier was created quoted. Cause 3.
Also confirm where you are, because it is embarrassingly common to be in the wrong place:
SELECT current_database(), current_user, current_schema(), inet_server_port();
SHOW search_path;Cause 1: wrong database
PostgreSQL databases are hard boundaries — you cannot query across them, and pg_class in postgres knows nothing about tables in app_production. Connecting with psql and no database name drops you into a database named after your OS user, or into postgres, neither of which has your tables.
$ psql -U app
psql (16.4)
app=> SELECT count(*) FROM users;
ERROR: relation "users" does not exist
LINE 1: SELECT count(*) FROM users;
^
app=> SELECT current_database();
current_database
------------------
appFix: connect to the right database, either with \c inside psql or -d on the command line. List what exists with \l:
app=> \l
List of databases
Name | Owner | Encoding | ...
----------------+-------+----------+-----
app | app | UTF8 |
app_production | app | UTF8 |
postgres | postgres | UTF8 |
app=> \c app_production
You are now connected to database "app_production" as user "app".
app_production=> SELECT count(*) FROM users;
count
-------
18342In application code, check the connection string. A DATABASE_URL of postgres://app:pw@host:5432/ with no trailing database name silently falls back to the user name. The difference between databases and schemas is a frequent source of this confusion — PostgreSQL schema vs database covers it in depth.
Cause 2: wrong schema or search_path
An unqualified name like users is resolved by walking search_path left to right. The default is "$user", public: first a schema named after the current user (if it exists), then public. If your table lives in a schema called app, PostgreSQL never looks there.
Reproduce:
CREATE SCHEMA app;
CREATE TABLE app.users (id serial PRIMARY KEY, email text);
SELECT * FROM users;ERROR: relation "users" does not exist
LINE 1: SELECT * FROM users;
^Four fixes, from most local to most permanent.
Schema-qualify the name. Always works, never depends on session state:
SELECT * FROM app.users;Set it for the session. Disappears when the connection closes:
SET search_path TO app, public;
SHOW search_path;
-- search_path
-- -------------
-- app, publicSet it for the role. Applies to every new session that role opens, in every database:
ALTER ROLE app SET search_path TO app, public;Set it for the database. Applies to every new session on that database, for every role:
ALTER DATABASE app_production SET search_path TO app, public;Role settings take precedence over database settings, and both only affect new connections — reconnect after running them. Verify what a role will get with:
SELECT rolname, rolconfig FROM pg_roles WHERE rolname = 'app';
SELECT d.datname, s.setconfig
FROM pg_db_role_setting s JOIN pg_database d ON d.oid = s.setdatabase;For migrations and cron jobs, prefer schema-qualified names. search_path is session state, and session state is exactly what connection poolers discard (see cause 7).
Cause 3: case sensitivity and quoted identifiers
PostgreSQL folds unquoted identifiers to lower case. CREATE TABLE Users creates users. But CREATE TABLE "Users" — with double quotes — creates a table whose name is literally Users, capital U, and from then on it can only be referenced with quotes. ORMs and GUI tools that quote every identifier are the usual culprit.
CREATE TABLE "Users" (id int);
SELECT * FROM users;ERROR: relation "users" does not exist
LINE 1: SELECT * FROM users;
^Note the error reports "users" in lower case — the folded name it searched for, not the name you typed. That is the tell. \dt shows the true spelling, and the catalog query with ILIKE finds it regardless of case:
app_production=> \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+-------
public | Users | table | app
app_production=> SELECT schemaname, tablename FROM pg_tables WHERE tablename ILIKE 'users';
schemaname | tablename
------------+-----------
public | UsersFix: quote it, or rename it once and stop quoting forever:
SELECT * FROM "Users"; -- works
ALTER TABLE "Users" RENAME TO users; -- permanent fixThe same rule applies to schema names ("App".users) and column names. Mixed-case identifiers are legal but a maintenance tax; lower_snake_case avoids the whole category.
Cause 4: migration not applied, or rolled back
If the catalog query returns nothing and the database is right, the table was probably never created. Two common reasons: the migration did not run against this environment, or it ran inside a transaction that rolled back. PostgreSQL DDL is transactional — a CREATE TABLE followed by any error in the same transaction leaves nothing behind.
BEGIN;
CREATE TABLE users (id serial PRIMARY KEY);
INSERT INTO users (id) VALUES ('abc'); -- fails
COMMIT; -- actually rolls back
SELECT * FROM users;ERROR: invalid input syntax for type integer: "abc"
LINE 1: INSERT INTO users (id) VALUES ('abc');
^
ROLLBACK
ERROR: relation "users" does not existCheck the migration tool's own state table to see what it believes has been applied:
-- Flyway
SELECT installed_rank, version, description, success, installed_on
FROM flyway_schema_history ORDER BY installed_rank DESC LIMIT 5;
-- Django
SELECT app, name, applied FROM django_migrations ORDER BY applied DESC LIMIT 5;
-- Prisma
SELECT migration_name, finished_at, rolled_back_at, logs
FROM _prisma_migrations ORDER BY started_at DESC LIMIT 5;
-- Rails / ActiveRecord
SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 5;A Flyway row with success = false, or a Prisma row with rolled_back_at set, means the migration is recorded but its objects are not there. Fix by repairing and re-running the migration (flyway repair, prisma migrate resolve, python manage.py migrate), against the environment you are actually querying.
Cause 5: ordering inside a script, CTE typos and sequence names
Within a single SQL file, statements execute top to bottom. Referencing a table before its CREATE TABLE fails with the same error, and so does a foreign key to a table defined further down. Reorder, or create tables first and add constraints with ALTER TABLE at the end.
CTE names are relations too, for the duration of the statement. A typo between the definition and the use raises 42P01:
WITH recent_orders AS (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT count(*) FROM recent_order;ERROR: relation "recent_order" does not exist
LINE 2: SELECT count(*) FROM recent_order;
^Sequences are the sneakiest case. serial and identity columns create a sequence, but its name follows the pattern table_column_seq truncated to 63 characters, and it moves when the table is renamed only if you rename it too. Hard-coding nextval('users_id_seq') after the table was renamed from accounts fails:
SELECT nextval('users_id_seq');
-- ERROR: relation "users_id_seq" does not existAsk PostgreSQL for the real name instead of guessing:
SELECT pg_get_serial_sequence('users', 'id');
-- pg_get_serial_sequence
-- ------------------------
-- public.accounts_id_seq
SELECT nextval(pg_get_serial_sequence('users', 'id'));Cause 6: is it really a permissions problem?
It is tempting to blame grants, but PostgreSQL is specific about this. A missing privilege produces SQLSTATE 42501 with a different message:
ERROR: permission denied for schema app
LINE 1: SELECT * FROM app.users;
^or permission denied for table users. If the object exists and you lack USAGE on its schema, you get 42501, not 42P01. Row-level security filters rows, it never hides the relation itself. And pg_catalog is readable by every role, so the diagnostic query at the top of this article will find the table even if you cannot select from it. If you see 42P01, the name genuinely did not resolve; stop looking at grants.
Cause 7: temporary tables and connection poolers
Temporary tables live in a per-session schema (pg_temp_N) and vanish when the session ends. If your application creates a temp table in one request and reads it in the next, it only works if both requests land on the same server connection.
Behind PgBouncer in pool_mode = transaction, they do not. Every transaction may be handed a different server connection, so the temp table — and any SET search_path you issued — belongs to a connection you no longer have:
CREATE TEMP TABLE staging_ids (id int);
-- ... transaction commits, PgBouncer returns the connection to the pool ...
SELECT * FROM staging_ids;
-- ERROR: relation "staging_ids" does not existThe error is intermittent, which is the giveaway: it works locally with a direct connection and fails in production under load. Fixes: wrap the create-and-use in a single transaction, use pool_mode = session for that pool, or replace the temp table with a CTE or an ordinary table keyed by a job id. The trade-offs are covered in the PgBouncer configuration guide and in PostgreSQL temporary tables.
The same applies to ON COMMIT DROP temp tables and to prepared statements, and to SET search_path (cause 2) — use SET LOCAL inside a transaction, or schema-qualify, when a pooler is involved.
Cause 8: views and materialized views over dropped tables
PostgreSQL will not let you drop a table that a view depends on without CASCADE — and CASCADE drops the view too. Weeks later, a report that used the view fails with 42P01 for the view's name, and nobody remembers it was collateral damage. Look at the server log for the original drop:
NOTICE: drop cascades to view active_usersFind the current dependents of a table before dropping it, so this does not happen again:
SELECT DISTINCT dependent_ns.nspname || '.' || dependent_view.relname AS dependent_view
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class dependent_view ON dependent_view.oid = r.ev_class
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
WHERE d.refobjid = 'public.users'::regclass
AND dependent_view.oid <> d.refobjid;A materialized view that survived (because it was dropped and recreated, say) but references a renamed table will keep its old definition and fail on REFRESH MATERIALIZED VIEW. Check with \d+ view_name and recreate from the corrected definition.
Diagnostic checklist
Run these in the order shown; the first one that surprises you is the answer.
-- 1. Where am I?
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();
-- 2. What will an unqualified name resolve against?
SHOW search_path;
SELECT current_schemas(true);
-- 3. Does the relation exist anywhere in this database, in any case?
SELECT n.nspname, c.relname, c.relkind
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ILIKE 'users';
-- 4. Does the name resolve right now? (NULL means no)
SELECT to_regclass('users'), to_regclass('app.users'), to_regclass('"Users"');
-- 5. Did the migration actually apply?
SELECT * FROM flyway_schema_history ORDER BY installed_rank DESC LIMIT 3;
-- 6. Am I behind a pooler that discards session state?
SHOW application_name; -- and check DATABASE_URL port: 6432 is usually PgBouncerto_regclass deserves a mention: it returns the OID if the name resolves under the current search_path and NULL if not, without raising an error. It is the fastest way to test a specific spelling.
When a schema has a hundred tables spread across several schemas, browsing them visually is quicker than scrolling \dt *.* — Chat2DB (opens in a new tab) shows every schema and its relations in a tree, with the case-exact names, so quoted identifiers and misplaced tables stand out immediately.
Summary
relation "x" does not exist means name resolution failed, nothing more. Confirm the database with current_database(), confirm the schema search order with SHOW search_path, and run the pg_class query with ILIKE to find the object wherever and however it is spelled. If it is genuinely absent, check the migration tool's history table for failed or rolled-back runs. If the failure is intermittent in production, suspect temp tables or SET commands behind a transaction-mode pooler. Schema-qualify names in migrations and jobs, avoid quoted mixed-case identifiers, and this error becomes rare and quick to close when it does appear.