relation does not exist in PostgreSQL (but it does)

ERROR: relation "orders" does not exist (SQLSTATE 42P01) means PostgreSQL could not resolve the name as you wrote it, with the search path in effect, for the role you are, in the database you are connected to, in the transaction you are in. When you can see the table in your database tool, one of those four differs between you and the code that failed. The common causes are a table created with capital letters in quotes, a table in a schema that is not on the search path, a schema the role has no USAGE on, and a connection to a different database.

We reproduced nine causes on PostgreSQL 17.11, each with the intended object provably present, and wrote one query that tells them apart.

Why does PostgreSQL say a table does not exist when it does?

Capital letters. PostgreSQL folds unquoted names to lower case, so select * from Users looks for users. A table created as "Users" only answers to "Users", with the quotes. The server log said relation "users" does not exist at character 15, pointing at the name.

Schema and search path. The default search path is "$user", public. A table in any other schema needs its schema in the name (app.orders) or in the role’s path: alter role r2 set search_path = app, public worked for new sessions, where current_schemas(true) became {pg_catalog,app,public}.

No USAGE on the schema. This is the case that looks most like a bug: the role has SELECT on the table, the schema is in search_path, and the table still “does not exist”. Without USAGE, PostgreSQL drops the schema from the effective path — show search_path still listed app, current_schemas(true) did not. grant usage on schema app to r3 fixed both the qualified and the unqualified query. The same thing happens, silently, when the schema in the path does not exist: set search_path = ap, public (a typo) or MyApp, public (folded to myapp) also left only {pg_catalog,public} and gave 42P01; has_schema_privilege tells the two apart. The same drop-out, for public itself, is measured in permission denied for schema public; the error that article is named after is about CREATE, not USAGE.

A search path set behind a pooler. Behind PgBouncer 1.26 in transaction mode, a session-level set search_path = app, public stayed on one server connection: the client’s next statement ran on another connection and got 42P01, and a different client that had never set it read app.orders from the first connection. set local search_path inside the transaction is the form that survives pooling; outside a transaction it only gave WARNING: SET LOCAL can only be used in transaction blocks, and the next query got 42P01.

Another database. PostgreSQL has no cross-database queries, so a table in database a does not exist for a connection to b. psql without -d connects to the database named after the role, unless PGDATABASE is set: as postgres that was postgres, as app it was FATAL: database "app" does not exist. In code, check the database name in the connection string before anything else.

Not committed, or temporary. A table created inside an open transaction is invisible to every other session, superuser included, until that transaction commits. A temporary table is never visible to another session.

Can the wrong table be found instead?

Yes, and then there is no error at all. A role app with its own schema app (so "$user" resolves to it) and a table orders in both app and public: as app, select count(*) from orders returned 42, from app.orders; as postgres, the same statement returned 1,000, from public.orders. 'orders'::regclass printed just orders in both cases; only relnamespace::regnamespace showed which one it was. In a multi-tenant setup with a schema per tenant, this is how one tenant’s query can read the shared table, or the reverse. The same rule decides where an unqualified create table lands: a migration role mig with its own schema mig created invoices there, although it could create in public, and the application role got relation "invoices" does not exist.

Why does CREATE INDEX on PostgreSQL 17 say the relation does not exist?

Because maintenance commands no longer use your search path. The 17.0 release notes: “Change functions to use a safe search_path during maintenance operations”, for ANALYZE, CLUSTER, CREATE INDEX, CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, REINDEX and VACUUM. A function that printed its search path showed "$user", public when called from a SELECT, and pg_catalog, pg_temp when called from CREATE INDEX. So an index on a SQL function whose body says select … from t failed on 17 already when it was created, not just after a restore — also for a table in public, although the release note speaks of “non-default schemas”. A PL/pgSQL function with the same body on an empty table was accepted, and inserts worked; the error came later, from ANALYZE and REINDEX. A function that calls another function of yours by its bare name gave function … does not exist instead. And a materialized view over such a function failed in CREATE MATERIALIZED VIEW; created WITH NO DATA, its first REFRESH failed.

A PL/pgSQL function with the same body, inside a CHECK constraint, survived on the source but broke the restore: the dump starts with set_config('search_path', '', false), the check ran while the data was loaded, pg_restore reported relation "t" does not exist and exited with 1, and the table came out empty — 0 rows against 1 in the source.

Three fixes worked for the index, each measured: a qualified name in the body (public.t), alter function code_ok_sql(text) set search_path = public, and a SQL-standard body (begin atomic … end), which is parsed when the function is created. For the CHECK, setting the search path on the function only changed the error into violates check constraint, because the dump loaded the checked table before the table the function reads; replacing the check by a foreign key made the restore succeed. A function that reads a table is not really IMMUTABLE; the index only accepted it because it was declared so. A CHECK accepts any function, but a check that reads another table is exactly what breaks a dump and restore, as measured here.

How do you find out which cause it is?

Run this as the role that got the error, in the database it was connected to, with the name as it appeared in the query:

-- psql -v name=orders -f diagnose.sql
select n.nspname                                         as schema,
       c.relname                                         as name_in_catalog,
       case c.relkind when 'r' then 'table' when 'p' then 'partitioned'
            when 'v' then 'view' when 'm' then 'matview' when 'S' then 'sequence'
            when 'f' then 'foreign' when 'i' then 'index' else c.relkind::text end as kind,
       c.relname = lower(:'name')                        as matches_unquoted,
       format('%I.%I', n.nspname, c.relname)             as write_as,
       has_schema_privilege(n.oid, 'USAGE')              as schema_usage,
       array_position(current_schemas(true), n.nspname)  as search_position,
       pg_table_is_visible(c.oid)                        as visible_unqualified,
       has_table_privilege(c.oid, 'SELECT')              as can_select,
       c.relpersistence = 't' and n.oid <> pg_my_temp_schema() as other_sessions_temp
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where lower(c.relname) = lower(:'name')
order by search_position nulls last, schema;

pg_class is readable by every role, also without USAGE on the schema, so the query sees what the failing statement could not.

Does the query return a row for the name?

• No rowNot in this database, for committed dataA typo, a renamed object, the wrong database (current_database()), or a CREATE that has not committed yet.
• A row with matches_unquoted = fCapital lettersWrite the name as in write_as, with the quotes.
• A row with schema_usage = fNo USAGE on the schemaThe schema drops out of the search path; grant USAGE.
• A row with no search_position, schema_usage = tSchema not on the search pathQualify the name, or set the role's search_path.
• A row with other_sessions_temp = tAnother session's temporary tableNothing reaches it.
• Two rows, the first one visible_unqualifiedThe bare name finds a different tableNo 42P01 but the wrong rows, or permission denied on the one it found.

Each branch checked against the setups in this article on PostgreSQL 17.11. Not covered by the query: the CREATE INDEX and restore cases, where the search path is not the role's.

For the role without `USAGE` on `app`, in a database where a second `orders` table also existed in `public`:

 schema | name_in_catalog | kind  | matches_unquoted |   write_as    | schema_usage | search_position | visible_unqualified | can_select | other_sessions_temp
--------+-----------------+-------+------------------+---------------+--------------+-----------------+---------------------+------------+---------------------
 public | orders          | table | t                | public.orders | t            |               2 | t                   | f          | f
 app    | orders          | table | t                | app.orders    | f            |                 | f                   | t          | f

The second row is the table the role is allowed to read, with schema_usage = f and no search position: the USAGE case. The first row is a different table that would have been found instead, and refused (can_select = f) — for this role the error was permission denied for table orders, not 42P01. For the "Users" table the query returned one row with matches_unquoted = f and write_as = public."Users". A row in a pg_temp_N schema with other_sessions_temp = t is another session’s temporary table: no grant or search path reaches it. No rows means no relation with that name exists in this database for anyone who can see committed data: a typo, a renamed object (like the sequence above), the wrong database, or a transaction that has not committed. Pass the name without its schema. When the table is found but a column is not, the message is column … does not exist instead; those causes are in column does not exist in PostgreSQL.

What we did not measure

Views and functions that reference a dropped or renamed table. ORMs that quote identifiers. Foreign tables.

Sources: PostgreSQL 17.0 release notes, migration section (safe search_path during maintenance operations), read on 21 October 2026. All measurements are our own, on PostgreSQL 17.11 in containers, on 21 October 2026.