How to Fix PostgreSQL Error: Relation Does Not Exist

The "Relation does not exist" error occurs when PostgreSQL cannot find the specified table, view, sequence, index, or other database object (collectively called "relations"). This is one of the most common PostgreSQL errors and usually indicates a typo, missing schema qualifier, or that the object hasn't been created yet.

Impact

This error prevents any operations on the specified relation, causing query failures, application errors, and potentially blocking entire features. It can occur during development, deployment, or after schema changes.

Common Causes

Table or view has not been created
Typo in relation name
Missing schema qualifier (e.g., using table instead of schema.table)
Case sensitivity issues with quoted identifiers
Relation in different schema than expected
Search path not including the relation's schema
Relation was dropped or renamed
Migration scripts not executed

Troubleshooting and Resolution Steps

List all relations in current database:

-- List tables in all schemas
\dt *.*

-- List only in public schema
\dt

-- List tables and views
\d

-- List in specific schema
\dt myschema.*

Search for relation by pattern:

-- Find tables matching pattern
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename LIKE '%customer%';

-- Include views
SELECT schemaname, viewname AS tablename
FROM pg_views
WHERE viewname LIKE '%customer%'
UNION
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename LIKE '%customer%';

Create the missing relation:

-- Create simple table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

-- Create table in specific schema
CREATE TABLE myschema.customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Use fully qualified name with schema:

-- Instead of
SELECT * FROM customers;

-- Use schema-qualified name
SELECT * FROM public.customers;
SELECT * FROM myschema.customers;

Check and set search_path:

-- Show current search path
SHOW search_path;

-- Set search path for session
SET search_path TO myschema, public;

-- Set permanently for user
ALTER USER username SET search_path TO myschema, public;

-- Set for database
ALTER DATABASE mydb SET search_path TO myschema, public;

Handle case-sensitive table names:

-- Unquoted names are converted to lowercase
CREATE TABLE MyTable;  -- Creates "mytable"
SELECT * FROM MyTable;  -- Looks for "mytable" (works)

-- Quoted names preserve case
CREATE TABLE "MyTable";  -- Creates "MyTable"
SELECT * FROM MyTable;   -- ERROR: looks for "mytable"
SELECT * FROM "MyTable"; -- Works correctly

-- Find exact table name
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name ILIKE 'mytable';

Check if relation exists before using it:

-- Check if table exists
SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name = 'customers'
);

-- Create if not exists
CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

Run missing migrations:

# Example with migration tools
# Flyway
flyway migrate

# Liquibase
liquibase update

# Alembic (Python)
alembic upgrade head

# Or manual SQL
psql -d mydb -f migrations/001_create_customers.sql

Restore from backup if table was accidentally dropped:

# Restore specific table from backup
pg_restore -d mydb -t customers backup.dump

# Or from SQL dump
psql mydb < backup.sql

Use information_schema to verify relation details:

-- Get table information
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_name = 'customers';

-- Get column information
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'customers' AND table_schema = 'public';

Additional Information

PostgreSQL relation names are case-insensitive unless quoted during creation
The default schema is public if not specified
System schemas (pg_catalog, information_schema) are in the default search path
Temporary tables are in a session-specific schema (pg_temp_*)
Use schema-qualified names in production code for clarity and reliability
Always include schema migrations in deployment processes

Frequently Asked Questions

Q: What is a "relation" in PostgreSQL?
A: In PostgreSQL, a relation is any table, view, materialized view, index, sequence, or foreign table. The term comes from relational database theory.

Q: Why does my query work in pgAdmin but not in my application?
A: Check the search_path setting. pgAdmin might have a different search path than your application connection. Use schema-qualified names or ensure consistent search_path settings.

Q: How do I rename a relation?
A: Use ALTER TABLE old_name RENAME TO new_name; Remember to update all queries and application code referencing the old name.

Q: Can I have tables with the same name in different schemas?
A: Yes, different schemas can contain tables with identical names. Access them using schema-qualified names: schema1.users vs schema2.users.

Q: What's the difference between a table and a view?
A: A table physically stores data, while a view is a stored query that dynamically retrieves data from underlying tables. Both are relations and can cause this error if they don't exist.

Q: How do I check if a relation exists in my code before using it?
A: Query information_schema.tables or use:

# Python with psycopg2
cursor.execute("""
    SELECT EXISTS (
        SELECT FROM pg_tables
        WHERE schemaname = 'public'
        AND tablename = 'customers'
    )
""")
exists = cursor.fetchone()[0]

Q: Why do I get this error after running migrations?
A: Ensure migrations ran successfully, check for errors in migration logs, verify you're connected to the correct database, and confirm the migration created tables in the expected schema.