PostgreSQL "relation does not exist": when the table is right there

1. A typo, or a plural

The most common one of all. Here, the table is called employees, and the query says employee:

-- ERROR: relation "employee" does not exist
SELECT * FROM employee;

SELECT * FROM employees LIMIT 3;

Search for it, with a wildcard, rather than guessing:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name ILIKE '%employee%';

In psql, \dt lists tables, and \dt *.* lists them in every schema.

2. Capital letters and quotes

Here’s the one that catches people. PostgreSQL lowercases names that aren’t in quotes. A tool or an ORM may create a table with quotes, keeping its capitals, and from then on it needs them each time:

CREATE TABLE "Customers" (id integer);

-- ERROR: relation "customers" does not exist
SELECT * FROM Customers;

SELECT * FROM "Customers";

Note the message: you wrote Customers, and it looked for customers. If you see that difference, this is your problem. So either quote it everywhere, or rename it, which saves trouble later on:

ALTER TABLE "Customers" RENAME TO customers;

SELECT * FROM customers;

Quotes also explain the opposite mistake. In PostgreSQL, double quotes always mean a name, while text goes in single quotes:

-- ERROR: column "Dublin" does not exist
SELECT * FROM departments WHERE location = "Dublin";

SELECT * FROM departments WHERE location = 'Dublin';

3. Another schema

A schema is a folder of tables inside a database. SELECT * FROM orders only looks in the schemas listed in search_path, and normally that’s your own and public:

SHOW search_path;

Here’s a table outside that path:

CREATE SCHEMA sales;
CREATE TABLE sales.invoices (id integer);

-- ERROR: relation "invoices" does not exist
SELECT * FROM invoices;

Name the schema, or add it to the path for this session:

SELECT * FROM sales.invoices;

SET search_path TO sales, public;
SELECT * FROM invoices;

To make that last one permanent for a user, run ALTER ROLE app SET search_path TO sales, public;, which applies from their next connection. A role with no rights on that schema gets permission denied for schema instead.

4. Another database

A connection only sees one database, and tables from another just don’t exist for it. It’s easy to end up in the default one, postgres, and not shop:

SELECT current_database();

In psql, use \l to list databases, and \c shop to switch. Check your connection string too, as postgresql://app@localhost/postgres connects to postgres, whatever you meant.

The same goes for the wrong server: a local one when you meant Docker, or staging when you meant production. SELECT inet_server_addr(), inet_server_port(); tells you which one you’re on. A local PostgreSQL and a container both on port 5432 is a classic trap.

5. It hasn’t been created

If it isn’t anywhere, then it may not exist yet:

• Migrations didn’t run on this environment, a typical failure after a fresh deploy. Check your tool’s status command, such as alembic current, prisma migrate status or rails db:migrate:status.
• A transaction was rolled back, taking the CREATE TABLE with it, since table creation is transactional in PostgreSQL. So a table you’ve just created is gone after a ROLLBACK.
• A temporary table only exists in the session that made it. With a connection pool, the next query can arrive on another connection, and not see it.
• A Docker volume was reset, leaving an empty database. Scripts in /docker-entrypoint-initdb.d only run on an empty data folder.

6. It’s there, and you can’t see it

If the table exists, and you lack rights on its schema, then you may get permission denied for schema, or a role without USAGE can get this same error for it. See permission denied for table.