How to Resolve "ERROR: Relation Does Not Exist" in Multi-Schema PostgreSQL

A query fails claiming a table doesn't exist, even though you can clearly see it when browsing the database. In a PostgreSQL database using multiple schemas, this almost always means the table exists — just not in the schema your current session is looking in by default.

The Problem

A query that should work fails with a relation error:

mydb=# SELECT * FROM orders;
ERROR:  relation "orders" does not exist
LINE 1: SELECT * FROM orders;
                      ^

But the table is clearly there when you look for it directly:

mydb=# \dt tenant_a.*
             List of relations
  Schema  |  Name  | Type  |  Owner
----------+--------+-------+---------
 tenant_a | orders | table | app_user

Why It Happens

PostgreSQL resolves an unqualified table name (one without a schema prefix) by searching through the schemas listed in the current session's search_path, in order, and uses the first match it finds. If the table you want lives in a schema that isn't in that path at all, PostgreSQL reports it as simply not existing — it doesn't search every schema in the database automatically. Common causes:

• The default search_path (typically "$user", public) doesn't include the schema where your table actually lives, especially in multi-tenant architectures using one schema per tenant.
• A connection pool or application reuses a session whose search_path was set for a different tenant or context than the one the current query expects.
• You're connected to the wrong database entirely, and a similarly named table exists elsewhere, adding to the confusion when browsing.
• The table was created in a specific schema explicitly, but subsequent queries were written assuming it would be findable in the default public schema.

The Fix

Check your session's current search_path:

SHOW search_path;

search_path
----------------
"$user", public

If the table's actual schema isn't listed, either qualify the table name explicitly in your query, which always works regardless of search_path:

SELECT * FROM tenant_a.orders;

Or set the search_path for your session to include the correct schema, which lets unqualified references resolve correctly going forward in that session:

SET search_path TO tenant_a, public;

SELECT * FROM orders;  -- now resolves to tenant_a.orders

For a multi-tenant application, set search_path per-connection based on which tenant the request is for, rather than relying on a single global default — most connection pool and ORM libraries support this via a connection initialization hook:

-- Example: PostgreSQL role-level default, per application user
ALTER ROLE tenant_a_user SET search_path = tenant_a, public;

If you'd rather set it persistently for the whole database session pattern used by your application (rather than per-role), configure it at the database level:

ALTER DATABASE mydb SET search_path = tenant_a, public;

Still Not Working?

If setting search_path doesn't resolve it, confirm you're actually connected to the correct database in the first place — a table can exist with the same name in a completely different database on the same PostgreSQL server, and no search_path configuration will bridge across databases (only across schemas within one database):

SELECT current_database();

Also search across every schema in the current database directly, to rule out a typo in the schema name you assumed the table was in:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = 'orders';

This lists every schema containing a table named orders, which quickly confirms whether the table exists where you expect, in a different schema than expected, or genuinely doesn't exist in the current database at all.

It's also worth being deliberate about the trade-offs of relying on search_path versus always qualifying table names explicitly, since both approaches are valid but suit different situations. Setting search_path keeps queries shorter and is convenient for interactive work or single-tenant applications, but in a multi-tenant architecture where the correct schema depends on runtime context (which tenant is making the request), relying on a mutable session-level setting introduces a subtle risk: if a connection pool reuses a session without resetting search_path between requests for different tenants, one tenant's query could silently run against another tenant's schema instead of failing loudly the way an explicit qualification would:

-- Safer for multi-tenant systems: always explicit, never relies on session state
SELECT * FROM tenant_a.orders WHERE customer_id = 42;

For multi-tenant systems specifically, many teams deliberately avoid relying on search_path for exactly this reason, preferring to pass the schema name as an explicit parameter in application code and interpolate it safely into fully-qualified queries, trading a bit of verbosity for a much lower risk of silent cross-tenant data leakage from a stale or misconfigured session.