Relation Does Not Exist Search Path
An unqualified statement such as SELECT ... FROM orders raises 42P01 relation "orders" does not exist, while the exact same statement works in a tool or...
In 10 seconds
A query that runs fine from one client suddenly fails from another with ERROR: relation "orders" does not exist (SQLSTATE 42P01) -- yet you can plainly see the table in your schema browser. The table is real and full of data. What changed is the lookup path: the table lives in a schema (here app) that is not on the failing session's search_path, so an unqualified reference to orders cannot be resolved.
Why This Happens
PostgreSQL resolves an UNQUALIFIED relation name by walking the schemas listed in search_path, in order, and using the first match. If none of those schemas contains a relation by that name, the parser raises 42P01. The default search_path is "$user", public, so an object created in a dedicated schema like app is invisible to any session that has not added app to its path.
The data is never missing; only the path the parser searches is wrong. The data is never missing; only the path the parser searches is wrong.
Diagnose
Free Tier -- Basic Approach
Two facts fully explain a 42P01 from an unqualified name: what is on the current search_path, and which schema actually owns the table. When the owning schema is missing from the path, the diagnosis is complete.
SHOW search_path;
search_path ----------------- "$user", public (1 row)
SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'orders';
schemaname | tablename ------------+----------- app | orders (1 row)
SHOW search_path returns the default "$user", public -- note that app is not listed. SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'orders' returns app | orders, proving the table exists but lives in a schema the session is not searching.
Fix
Put the owning schema on the search_path for the database so future sessions resolve unqualified references. ALTER DATABASE ... SET search_path persists the setting for new connections; it rewrites no data.
SELECT format('ALTER DATABASE %I SET search_path = app, public', current_database()) \gexec
SET search_path = app, public;
SELECT count(*) AS rows_via_unqualified FROM orders;
ALTER DATABASE
SET
rows_via_unqualified
----------------------
1000
(1 row)
ALTER DATABASE <db> SET search_path = app, public (issued via \gexec with the current database name) persists the path. The same capture then SETs the path in-session and runs SELECT count(*) FROM orders, which now returns 1000 -- the identical unqualified query that failed before now resolves because app is on the path.
Verify
Confirm the fix from a brand-new connection -- the situation that originally failed -- and confirm the table truly depends on the path by removing the schema again.
SHOW search_path; SELECT count(*) AS rows_via_unqualified FROM orders;
search_path
-------------
app, public
(1 row)
rows_via_unqualified
----------------------
1000
(1 row)
SET search_path = pg_catalog, public; SELECT count(*) FROM orders;
SET
ERROR: 42P01: relation "orders" does not exist
LINE 1: SELECT count(*) FROM orders;
^
LOCATION: parserOpenTable, parse_relation.c:1449
This capture is a fresh session: SHOW search_path returns app, public (inherited from the ALTER DATABASE setting with no SET in this session), and SELECT count(*) FROM orders returns 1000. Then SET search_path = pg_catalog, public removes app and the same query raises 42P01 again -- proving the fix was the path, not the data.
⏪ Rollback
The durable fix (ALTER DATABASE ... SET search_path = app, public) is reversible with ALTER DATABASE ... RESET search_path (or by setting it back to the previous value); it only changes the default for new connections and rewrites no data. The schema-qualified form (app.orders) is purely additive in application code and changes no server state. Nothing here touches table contents, so there is no data to restore.
📦 Version Matrix (PG 14--18)
The same name-resolution rule applies on every supported release: an unqualified reference with the schema off the path fails with 42P01, and the reference resolves once the schema is on the path.
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=relation "orders" does not exist | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=relation "orders" does not exist | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=relation "orders" does not exist | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=relation "orders" does not exist | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=relation "orders" does not exist | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Why does it work in psql but fail from my application?
The two connections have different search_path values. psql may be connecting as a role whose path includes app (or you ran SET earlier), while the application connects with the default "$user", public. Compare SHOW search_path in both.
Should I add the schema to search_path or just qualify the table name?
Qualifying (app.orders) is the most robust because it resolves regardless of path and is immune to later config changes. Setting search_path is convenient when many statements target one schema; persist it with ALTER DATABASE or ALTER ROLE so new sessions inherit it.
How do I find which schema actually owns the table?
Query SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'orders'; or use to_regclass on candidate qualified names. to_regclass('orders') returns NULL when the unqualified name is off the current path.
Does ALTER DATABASE ... SET search_path affect sessions that are already open?
No. It changes the default for connections opened afterward. Existing sessions keep their current path until they reconnect or run SET themselves.