PostgreSQL — error 42P01 relation does not exist

Resolve PostgreSQL 42P01 by checking search_path, schema qualification and whether the migration created the table in the expected schema.

beginnerdebuggingpostgressql

Symptoms

ERROR: relation "users" does not exist
The table exists in one database but not the one the app uses
It works after setting search_path but not by default
The search path resolves to only a schema without the table

Possible causes

The table is in a schema not on the search_path
Migrations ran against a different schema or database
The connection sets a search_path that excludes the table schema
The object name case differs because it was created quoted

Diagnosis steps

List tables visible to this session

psql -c "\dt *.users" ; psql -c "select current_schema(), current_schemas(true)"

Interpretation

Printing the search path and locating the table in specific schemas shows whether it is present but unqualified or in another schema.

Search all schemas for the relation

psql -c "select schemaname, tablename from pg_tables where tablename = 'users'"

Interpretation

Results name the schema that owns the table, so you can qualify the query or fix the search path.

Check the migration target

grep -rn "search_path\|ALTER ROLE\|CREATE SCHEMA" migrations/

Interpretation

A migration that created the table in a non-default schema explains why the unqualified name fails.

Root cause

The relation was not visible under the session search_path, either because the table lives in a different schema or was created in a schema or database other than the one the connection uses.

Fix

Qualify the table with its schema, add the schema to the search_path for the role or session, or re-run migrations against the correct schema and database.

Verify the fix

Run the failing query again and confirm it resolves, and confirm the search path and table schema now agree.

Symptom walkthrough

A query fails with ERROR: relation does not exist even though the table exists. PostgreSQL resolves unqualified names through the search_path, so a table in a schema that is not on the path is invisible, which is why qualified queries may work while unqualified ones fail. Case also matters: an identifier created with quotes is case-sensitive and will not match an unquoted lookup.

Fix in practice

Qualify the table with its schema, or change the role or session search_path to include that schema. If migrations created the table elsewhere, re-run them against the intended schema and database. Confirm whether quoted identifiers introduced a naming mismatch.

Verification

Run the failing query again and confirm it resolves, then check the search path and the table schema to confirm they now agree. A resolving query with a matching schema confirms the fix.

Diagnostic commands

Check the behavior with commands like these:

psql -c "\dt *.users" ; psql -c "select current_schema(), current_schemas(true)"
psql -c "select schemaname, tablename from pg_tables where tablename = 'users'"
grep -rn "search_path\|ALTER ROLE\|CREATE SCHEMA" migrations/