PostgreSQL 42P01: relation does not exist
PostgreSQL can't find the table, view or sequence. Usually the wrong schema on the search_path, a quoted mixed-case name, or the wrong database.
- SQLSTATE
42P01- Name
undefined_table
What it means
In PostgreSQL a relation is any table-like object: a table, view, materialized view, sequence, index or foreign table. 42P01 means the name you used doesn't resolve to one, in the schemas listed on your search_path (or in the schema you named explicitly).
PostgreSQL folds unquoted names to lower case. A table created as "Orders" (quoted, capital O) can only be referred to as "Orders", so SELECT * FROM Orders looks for orders and fails.
Common causes
- The table is in a schema that isn't on the
search_path, for examplesales.orderswhile the path is"$user", public. - The table was created with a quoted mixed-case name (common with ORMs and GUI tools) and the query doesn't quote it.
- You're connected to a different database than the one that has the table. PostgreSQL can't query across databases with a plain name.
- The table hasn't been created yet, or a migration failed or ran in another database.
- A typo in the table name.
How to fix it
-
Check where you are and what your search path is.
SELECT current_database(), current_schema(); SHOW search_path;
-
Find the table, whatever its schema or casing.
SELECT table_schema, table_name FROM information_schema.tables WHERE lower(table_name) = 'orders';
-
Qualify the name with its schema, or add the schema to the search path.
SELECT * FROM sales.orders; SET search_path TO sales, public; -- permanently for a role: ALTER ROLE app SET search_path TO sales, public;
-
If the table was created with capitals, quote it exactly, or rename it to lower case once so it never needs quotes.
SELECT * FROM "Orders"; ALTER TABLE "Orders" RENAME TO orders;
Reproduce it safely
SELECT * FROM table_that_does_not_exist; -- ERROR: relation "table_that_does_not_exist" does not exist
Questions
The table exists, so why does PostgreSQL say relation does not exist?
Almost always the schema or the case. Either the table's schema isn't on your search_path, or it was created with a quoted mixed-case name and must be quoted the same way. Querying information_schema.tables with lower(table_name) shows its real schema and spelling.
Can I select from a table in another database?
Not directly. Each PostgreSQL connection is to one database. Use the postgres_fdw or dblink extensions, or connect to the other database.
Related errors
42703column does not exist3F000schema does not exist3D000database does not exist42501permission denied208SQL Server: Invalid object name1146MySQL: Table doesn't existORA-00942Oracle: table or view does not exist
More syntax error or access rule violation errors
42601syntax error at or near42846cannot coerce42803column must appear in the GROUP BY clause42830invalid foreign key42804datatype mismatch42P18could not determine data type of parameter42883function does not exist42P02there is no parameter
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.