// PostgreSQL error reference

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
Category
Syntax Error or Access Rule Violation
Error message
relation "orders" does not exist
LINE 1: SELECT * FROM orders;
                      ^

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 example sales.orders while 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

  1. Check where you are and what your search path is.

    SELECT current_database(), current_schema();
    SHOW search_path;
  2. Find the table, whatever its schema or casing.

    SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE lower(table_name) = 'orders';
  3. 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;
  4. 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

More syntax error or access rule violation errors

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.

// fewer errors, faster fixes

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.