relation not found - How to fix
This video goes through each step and explains a few things along the way.
relation not found error means Postgres can’t find the table you’re asking for.
There are two common causes for this error:
- Spelling errors
- Double check the query.
- Double check the table name.
- Wrong location
- Ensure you’re querying the correct database.
- Ensure you’re querying the correct schema.
This page shows how to ensure you’re querying in the right location.
A refresher on database structure
Databases contain schemas, and schemas contain tables.
- An instance of Postgres can contain many databases.
- The default database name is
- You can create other databases with any name you want.
- You cannot have two databases with the same name in Postgres.
- Each database can contain many schemas.
- The default schema name is
- Just like databases, you can create schemas with any name you want.
- You can have schemas with the same name as long as they’re in different databases.
- Each schemas can contain many tables.
- There is no default table name, you must name them.
- You can have tables with the same name as long as they’re in different schemas.
Now that we have reviewed the structure of Postgres, let’s start troubleshooting.
Ensure you’re in the correct database
Here are the commands I use to ensure I’m in the right database:
Check which database you’re querying
Show all databases
Change to another database
Ensure you’re in the correct schema
Check which schema you’re in
Show available schemas
SELECT schema_name FROM information_schema.schemata;
This command will show system tables like
pg_toast that you shouldn’t worry about.
SET search_path to whatever_schema_name
Show all tables in your current schema
Search schemas for table by name
You can search for tables with a certain name in the current database with:
SELECT table_name, table_schema FROM information_schema.tables WHERE table_name = 'table_to_find' AND table_type='BASE TABLE';