Fix: PostgreSQL 'relation does not exist' Error

Problem

You query a table you know exists and PostgreSQL says otherwise:

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

Symptoms

  • \dt in psql shows the table
  • The query works in one connection but fails in another
  • The table was created by a migration but isn’t visible
  • Table names with uppercase letters cause this error

Root Cause

This error has three common causes:

  1. Schema search path — The table is in a non-default schema and your search_path doesn’t include it
  2. Missing migration — The table was never created (different database, dropped accidentally)
  3. Case sensitivity — PostgreSQL folds unquoted identifiers to lowercase, but quoted names preserve case

Solution

Step 1: Check what schemas exist

\dn
-- List all schemas

SELECT schema_name FROM information_schema.schemata;

Step 2: Find where your table lives

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = 'users';

If the table is in a schema like app or public, note the schema name.

Step 3: Check your search path

SHOW search_path;
-- Default: "$user", public

SELECT current_schemas(true);

If your table is in the app schema and app is not in the search path, you have two options:

Qualify the table name:

SELECT * FROM app.users;

Extend the search path:

SET search_path TO app, public;
-- Or permanently for this database:
ALTER DATABASE mydb SET search_path TO app, public;

Step 4: Handle case-sensitive names

If someone created a table with quotes, the name is case-sensitive:

-- Created with quotes — "Users" with capital U
CREATE TABLE "Users" (id SERIAL);

-- These fail:
SELECT * FROM Users;     -- folded to 'users'
SELECT * FROM users;     -- 'users'

-- This works:
SELECT * FROM "Users";   -- exact match

Fix: Rename the table to lowercase:

ALTER TABLE "Users" RENAME TO users;

Verification

-- Check the table exists and is accessible
SELECT * FROM app.users LIMIT 1;

-- Permanently set search path if needed
ALTER ROLE myuser SET search_path TO app, public;

-- Reconnect and verify
-- Then: SELECT * FROM users;  -- should work now

Prevention

  • Always use lowercase table names — avoids case-sensitivity issues
  • Explicitly set search_path in your application’s database connection string or pool config
  • Use fully qualified names (schema.table) in critical queries
  • Run migrations against the correct database — confirm \c mydb in psql
  • Document schema conventions in your project’s README

References

Advertisement