Postgres relation not found - How to fix
This video goes through each step and explains a few things along the way.
The 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.
-
Databases
- An instance of Postgres can contain many databases.
- The default database name is
postgres. - You can create other databases with any name you want.
- You cannot have two databases with the same name in Postgres.
-
Schemas
- Each database can contain many schemas.
- The default schema name is
public. - 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.
-
Tables
- 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
SELECT current_database();
Show all databases
\l
Change to another database
\c database_name
Ensure you’re in the correct schema
Check which schema you’re in
SHOW search_path
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.
Change schema
SET search_path to whatever_schema_name
Show all tables in your current schema
\d
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';