PostgreSQL 'relation does not exist': Cause and Fix

A query that worked in a test database fails in production, or one that worked yesterday fails after a deploy. On PostgreSQL 17 (16 prints the same):

SELECT * FROM users;

sql

ERROR:  relation "users" does not exist
LINE 1: SELECT * FROM users;
                      ^

Text

The SQLSTATE is 42P01 (undefined_table). The message is literal: PostgreSQL looked in the schemas on its search_path and found no object named users. It says nothing about where the table actually is, which is why the first job is to find it.

Quick fix checklist

Confirm you are on the database you think: SELECT current_database(), current_user;.
Find the table anywhere it might be: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'users'; (or search information_schema.tables for views too).
See your resolution path: SHOW search_path; and SELECT current_schemas(false);.
If the table is in app, either qualify it (SELECT * FROM app.users;) or add the schema to the path (SET search_path TO app, public;).
Check the identifier’s case: an object created as "Users" needs the quotes and exact case every time.
Confirm migrations ran on this database (a fresh environment, a different branch or a replica often has none).
After a schema move, refresh cached plans: DISCARD PLANS; or reconnect.

Before you start

You need psql (or a SQL client) connected as a role that can read pg_catalog and information_schema, which every role can. The examples use PostgreSQL 16 and 17; this error behaves the same since long before. ORMs add a layer: the connection they open may set a different search_path or point at a different database than your psql session, so always reproduce with the application’s own connection when you can.

Why it happens

PostgreSQL resolves every unqualified name (users) by walking the search_path in order and using the first schema that has a matching object. If none does, you get 42P01. The object can be missing for several distinct reasons that all produce the same text:

A schema that is not on the path. The table lives in app, but the session’s search_path is "$user", public. This is the most common cause after moving tables out of public.
A different database. PostgreSQL does not query across databases. A table in billing is invisible to a connection to analytics, even on the same host.
Migrations that never ran here. A new environment, a replica, or a branch database can be empty while the application assumes a schema.
Case sensitivity. Unquoted names are folded to lower case. A table created with CREATE TABLE "Users" (...) is stored as Users and will not match users. You must quote it exactly (SELECT * FROM "Users";) forever after.
A dependency dropped or renamed. A destructive migration removed the table, or renamed it without updating the code.

Step-by-step walkthrough

Step 1: Confirm the database and user

SELECT current_database(), current_user, inet_server_addr(), inet_server_port();

sql

This rules out “I am connected somewhere else”, especially when production and staging share a host or a tunnel.

Step 2: Find where the object actually is

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name ILIKE 'users';

sql

ILIKE makes the search case-insensitive, which also reveals a mixed-case name. Include views: information_schema.tables covers tables and views together. If this returns nothing anywhere, the object really is missing and you need to run migrations.

Step 3: Inspect the search path

SHOW search_path;
SELECT current_schema();       -- the first schema that will hold new objects
SELECT current_schemas(false); -- the schemas actually searched, minus system ones

sql

If the table is in a schema that is not listed, that is the whole problem.

Step 4: Fix the resolution, not just this query

-- one-off, this session only
SET search_path TO app, public;
-- permanent for a role
ALTER ROLE app_user SET search_path TO app, public;
-- or qualify every reference in the code
SELECT * FROM app.users;

sql

Qualifying in the application is the most robust option, because it does not depend on a session setting that a connection pool or a migration tool can change. If you set a database or role default, remember it only applies to new sessions.

Step 5: Handle case and confirm migrations ran

If Step 2 found the table as Users, the case is the issue. Either quote it exactly (SELECT * FROM "Users";) or rename it to lower-case once: ALTER TABLE "Users" RENAME TO users;. Prefer the rename, because mixing quoted and unquoted names is a lasting source of this error. Tools that generate SQL often fold identifiers, so check the ORM’s quoting rule as well.

Make sure migrations ran here.

# the runner is whatever your project uses
npx prisma migrate deploy
# or
migrate -path ./migrations -database "$DATABASE_URL" up

Terminal

Point the migration DATABASE_URL at the same database the application uses. A green deploy pipeline can still leave a database un-migrated if the step targeted a different environment.

Worked scenario

An application starts fine but every request logs relation "orders" does not exist right after a release that moved tables from public into an app schema. SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'orders' shows app | orders, while SHOW search_path shows "$user", public. The table exists and PostgreSQL is looking in the wrong place. The code was written with unqualified names. The durable fix is to qualify the queries (app.orders) or to set search_path on the application role; a one-off SET search_path in an ad-hoc session does not help the application, because its pooled connections never see it.

Common mistake

Adding the schema to search_path in a psql session to make the error go away, then closing the terminal. The application still uses its own connections, so nothing changes for users. Worse, if two schemas both hold a users table and you reorder search_path, queries start hitting the other one silently. Qualify names in code, and keep search_path as a deliberate default rather than a rescue.

Verify the behavior

The same unqualified query should now resolve, and here is how to see what it resolves to:

SELECT current_schemas(false);
SELECT 'app.users'::regclass;   -- errors loudly if it cannot be resolved
SELECT * FROM users LIMIT 1;

sql

{app,public}
app.users
(1 row)

Text

regclass is the precise check: it resolves the name the way the parser will, or raises 42P01 itself.

Interview exercise

“After a deploy, one service logs relation \"payments\" does not exist while every other service is fine. The table is in the same database. How do you narrow it down?”

Answer and reasoning

If the table is in the same database and only one service fails, the difference is in that service’s session, not the data. Its connection must be resolving names differently: a different search_path (often because it logs in as a different role or sets search_path in its connection string), a different schema owner after the move, or a case-mismatch introduced by an ORM that quotes identifiers. I would confirm the object exists with SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'payments', then inspect that service’s connection: its role, its search_path, and the exact SQL it sends with log_statement or the driver’s query log. Comparing the failing SQL with the working services usually shows an unqualified name in one and a qualified name in the others, which points straight at the fix: qualify the query or set the role’s search_path.