relation does not exist

This error occurs when you reference a table, view, or sequence that PostgreSQL cannot find in the current schema.

The relation does not exist error means PostgreSQL can't find the table, view, or other database object you're trying to use.

Understanding the Error

ERROR: relation "users" does not exist
LINE 1: SELECT * FROM users;
                      ^

The object doesn't exist, or exists in a different schema.

Common Causes

1. Table Doesn't Exist

SQL

-- Table was never created or has a different name
SELECT * FROM users;  -- Error if 'users' doesn't exist

2. Wrong Schema

SQL

-- Table exists in different schema
SELECT * FROM users;  -- Error: looking in public schema
SELECT * FROM myschema.users;  -- Works!

3. Case Sensitivity

SQL

-- Table was created with quotes (case-sensitive)
CREATE TABLE "Users" (id INT);
SELECT * FROM users;  -- Error: looking for lowercase 'users'
SELECT * FROM "Users";  -- Works!

4. Different Database

Connected to wrong database where the table doesn't exist.

5. Table Not Yet Created in Migration

Accessing table before migration has run.

How to Fix It

Solution 1: Check Table Exists

SQL

-- List all tables in current schema
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public';

-- Or use psql command
\dt

Solution 2: Create the Table

SQL

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(100)
);

Solution 3: Use Correct Schema

SQL

-- Set search path to include your schema
SET search_path TO myschema, public;

-- Or qualify the table name
SELECT * FROM myschema.users;

Solution 4: Check Case Sensitivity

SQL

-- Find exact table name
SELECT table_name FROM information_schema.tables
WHERE LOWER(table_name) = 'users';

-- Use quotes for case-sensitive names
SELECT * FROM "Users";

Solution 5: Verify Database Connection

SQL

-- Check current database
SELECT current_database();

-- List all databases
\l

Solution 6: Check Migrations

BASH

# Run pending migrations (example with common tools)
npx prisma migrate dev
# or
npx knex migrate:latest
# or
rails db:migrate

Finding Tables

SQL

-- All tables in all schemas
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

-- Tables matching pattern
SELECT table_name FROM information_schema.tables
WHERE table_name LIKE '%user%';

Best Practices

Use consistent naming (lowercase, snake_case recommended)
Avoid quoted identifiers unless necessary
Set search_path appropriately in connection string
Use IF NOT EXISTS when creating tables
Check migrations have run in new environments