Fix PostgreSQL 'relation does not exist' error

If you have a PostgreSQL database and a table named Car for example and you try doing

SELECT * FROM Car

you’ll see an error saying

Query 1 ERROR: ERROR:  relation "car" does not exist
LINE 1: SELECT * FROM Car

PostgreSQL raises this error when it cannot resolve the table name in the current connection. That has a few distinct causes, and the error text already contains a clue: the name in quotes is what PostgreSQL actually looked for.

Check you are in the right place

One issue might be the table actually does not exist — in this database. With several environments around, it is easy to run a query against postgres while the table lives in your application database. Check where you are and what is visible:

SELECT current_database(), current_schema();
SHOW search_path;
\dt *.*

If the table exists in a schema like app but you query it as a bare name, either qualify it (app.car) or add the schema to your search_path. If it is missing entirely, run your migrations against this database.

The mixed-case trap

But if the table does exist, this error usually appears because PostgreSQL folds unquoted identifiers to lowercase.

SELECT * FROM Car looks for car. A table created as CREATE TABLE Car really is car, so that works. A table created with quotes — CREATE TABLE "Car", which ORMs and GUI tools like to do — keeps its capital C, and only a quoted query finds it.

Use this syntax instead:

SELECT * FROM "Car"

My advice for your own schemas: use lowercase unquoted names everywhere, and the folding rules stop mattering. Only add quotes when you know the table was created with a quoted mixed-case name, because quoting a query against an ordinary lowercase table recreates the same error in reverse.

Want me to talk about your product? You can sponsor this site.