Incident Response

PostgreSQL "relation does not exist": 7 Causes and How to Fix Them

The table is right there in your GUI, yet PostgreSQL says it does not exist. Almost always the name is resolved differently from what you expect: wrong database, wrong schema, a capital letter, a pooler that swapped your search_path, or a restore that runs with a stricter path.

· 12 min read · AG Data Team

"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;

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

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

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:

Key takeaways

PostgreSQL Errors Schemas search_path
Work with us

Migrations or restores failing on missing relations?

We untangle schemas, search_path and pooler settings, fix restores and migrations that break on name resolution, and make multi-schema setups predictable.

Get in touch More from the blog