"relation does not exist" is PostgreSQL saying it could not resolve a table, view or sequence name in the current session. "Relation" is PostgreSQL's word for anything that looks like a table. The object usually does exist; the session just looks for it in a different place than you do. The SQLSTATE is 42P01, undefined_table (error codes).
ERROR: relation "orders" does not exist
LINE 1: SELECT * FROM orders;
^
Start with three questions
-- where am I?
SELECT current_database(), current_user;
SHOW search_path;
SELECT current_schemas(true); -- the path as actually resolved
-- does a table with this name exist anywhere, in any case?
SELECT n.nspname AS schema, c.relname, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE lower(c.relname) = lower('Orders');
-- NULL instead of an error if the name does not resolve
SELECT to_regclass('orders'), to_regclass('app.orders');
Which database, which user, which search path. current_schemas(true) is more honest than SHOW search_path: it shows how the path was actually resolved, omits entries that do not correspond to existing schemas and, with true, includes implicit ones like pg_catalog (system information functions). to_regclass returns NULL instead of an error, which makes it handy in scripts and health checks. The pg_class query looks in every schema regardless of case, so it answers "does this exist at all?" in one go.
1. A capital letter in the name
CREATE TABLE "Orders" (id bigint); -- created by an ORM or a GUI tool
SELECT * FROM Orders; -- ERROR: relation "orders" does not exist
SELECT * FROM "Orders"; -- works
Unquoted names are always folded to lower case; quoted names are case-sensitive. FOO, foo and "foo" are the same identifier, while "Foo" and "FOO" are different (lexical structure). ORMs, GUI tools and migrations from other databases often create mixed-case names with quotes, and every hand-written query without quotes then fails. Notice the error message: it shows the folded name "orders", which is the giveaway. The long-term fix is to rename to lower case; the short-term one is to quote consistently. The documentation's advice for portable code is to either always quote a name or never quote it.
2. The table is in a schema that is not on the search path
An unqualified name is looked up in the schemas of search_path, in order. The default is "$user", public; the $user entry is ignored if no schema with the user's name exists (schemas). A table in app or billing is invisible to an unqualified query until you qualify it or change the path:
-- per role (takes effect for new sessions)
ALTER ROLE app_user SET search_path = app, public;
-- per database
ALTER DATABASE shop SET search_path = app, public;
-- per function: do not depend on the caller at all
ALTER FUNCTION app.normalize_sku(text) SET search_path = app, pg_catalog;
- Role and database settings apply to new sessions, so long-lived pooled connections keep the old value until they reconnect.
- Adding a schema to
search_patheffectively trusts everyone who hasCREATEon it, as the documentation warns. Keep paths short and avoid writable shared schemas in them. PostgreSQL 15 no longer gives everyoneCREATEonpublicin new databases; the related "permission denied" error is covered in permission denied for schema public. - Unqualified
CREATE TABLEgoes into the first existing schema in the path. A wrong path at migration time means the table is created somewhere else, and the application then cannot find it.
3. Wrong database
In PostgreSQL every database is separate: a session connected to postgres cannot see tables in shop, and you cannot join across databases without an extension such as postgres_fdw. In our experience this is the most common cause when someone connects with psql without -d, or when an environment variable points the app at the wrong database after a deploy. current_database() settles it in a second.
4. Temporary tables and uncommitted DDL
- A temporary table exists only in the session that created it. The session's temporary schema is searched first, before
pg_catalog, unless it is listed explicitly, but only for relations and types, never for functions (search_path). Another connection from the pool will not see it. - DDL in PostgreSQL is transactional. A table created inside a transaction that has not committed is invisible to every other session, and a rollback removes it completely.
5. PgBouncer transaction pooling
In transaction mode each transaction can run on a different server connection. The PgBouncer feature matrix lists SET/RESET as never supported in transaction pooling (PgBouncer features). A SET search_path at the start of the session may therefore apply to one server connection and be missing on the next, which produces intermittent "relation does not exist" errors that nobody can reproduce in psql. According to the PgBouncer documentation, it tracks search_path only with PostgreSQL 18 or later, because older versions do not report changes of it to the client (PgBouncer configuration). Safer options: set the path per role or database, or qualify names. More on pooling modes in PgBouncer connection pooling.
6. Restores and maintenance that run with a stricter path
Since the CVE-2018-1058 fixes in PostgreSQL 10.3 and the parallel minor releases, pg_dump, pg_upgrade, vacuumdb and similar tools put only pg_catalog in their search path, and autovacuum workers do the same (release notes 10.3). A user function that refers to a table without a schema works in the application, but fails when it is called from an index expression or a materialized view during a restore or auto-analyze. The release notes say such functions must not assume any search path. Fix the function by schema-qualifying references or attaching SET search_path to it, as in the last line of the example above.
If a restore stops on "relation does not exist", look at the object being restored, not at the table named in the error. In our experience it is usually an index or materialized view built on a function with an unqualified table reference.
7. Replicas and logical replication
- A physical replica that is lagging has simply not replayed the
CREATE TABLEyet. Applications that read from replicas right after a migration see the error for as long as the lag lasts. - Logical replication does not replicate the schema or DDL (logical replication restrictions). A table added on the publisher has to be created on the subscriber by hand before it can be replicated; otherwise the subscriber fails with
logical replication target relation "public.x" does not exist.
What changes with many schemas and big deployments
In a test database with one schema and five tables none of this shows up. In production:
- Schema-per-tenant. With hundreds or thousands of tenant schemas, a migration that fails half-way leaves some tenants without the new table. The error then appears only for some customers. Run tenant migrations with a per-schema status table and check with
to_regclassin every schema after the rollout. - Search path as routing. Selecting the tenant through
search_pathworks with session pooling, and per the PgBouncer docs is tracked with PostgreSQL 18 or later. On older versions with transaction pooling it is a data-leak risk, not just an error source: a query can land in the wrong tenant's schema. In our experience schema-qualified names or one role per tenant are safer. - Deploy order. Code that expects a new table must not reach production before the migration has committed on the primary and replayed on the replicas the code reads from.
- Restores of large dumps. A restore that fails after three hours on one function-based index is expensive. Test restores of production-sized dumps regularly, and check function definitions for unqualified references before a major upgrade.
Key takeaways
- Check
current_database(),current_userandcurrent_schemas(true)first. - Unquoted names are folded to lower case; a mixed-case table needs quotes every time.
- Set
search_pathper role, database or function, not withSETbehind a transaction pooler. - Dump, restore, vacuum tools and autovacuum use a
pg_catalog-only path; functions must qualify their table references. - Replicas lag and logical replication does not copy DDL: deploy schema first, then code.