Victor's room

Overview & Root Cause Summary: The error ERROR: relation "<table_name>" does not exist (LINE 1: SELECT * FROM <table_name>) occurs in PostgreSQL when a query, ORM, or application attempts to reference a table, view, sequence, or index that cannot be resolved in the database’s current namespace. While it can mean a table was never created, the most frequent real-world causes are schema isolation mismatches (where tables reside in a custom schema omitted from search_path), PostgreSQL’s identifier lowercase folding behavior conflicting with quoted mixed-case names (e.g. "Users" vs users), or missing schema USAGE permissions for the connecting user role.

Understanding the Root Causes

• Omitted Schema in search_path: PostgreSQL groups tables into logical schemas (e.g., public, auth, tenant1). If tables exist in a custom schema not included in the active search_path, queries omitting the schema prefix (SELECT * FROM users instead of SELECT * FROM auth.users) fail immediately.
• Case Sensitivity and Quoted Identifier Folding: PostgreSQL automatically folds unquoted SQL identifiers to lowercase. If a table was created using double quotes with capital letters (e.g., CREATE TABLE "Users" (...)), querying SELECT * FROM Users folds to lowercase users, resulting in relation does not exist.
• Missing Schema USAGE Privileges: In PostgreSQL 15+, public schema permissions were tightened. If the connecting database user lacks the USAGE privilege on the containing schema, PostgreSQL conceals the relation and reports it as non-existent.
• Database Target Mismatch: Connecting to the default postgres maintenance database instead of the application’s provisioned target database.

Step 1: Quick Fix (Inspect Schemas & Adjust search_path)

Identify where the relation actually resides across database schemas and configure your search path to include it.

# 1. Connect to PostgreSQL and list all tables across all schemas:
psql -U myuser -d mydb -c "\dt *.*"

# 2. Or query the information schema catalog:
SELECT table_schema, table_name 
FROM information_schema.tables 
WHERE table_name ILIKE '%users%';

# 3. If the table resides in a custom schema (e.g. 'app'):
# Set search_path for the active session:
SET search_path TO app, public;

# 4. Make the search_path permanent for the database user:
ALTER ROLE myuser SET search_path TO app, public;

# 5. Or configure default search_path database-wide:
ALTER DATABASE mydb SET search_path TO app, public;

Step 2: Resolve Identifier Case Folding & Double-Quote Mismatches

Align query syntax with how identifiers were created, or normalize mixed-case table names to standard lowercase.

# 1. Check if the table was created with quoted casing:
# In psql, list exact table casing:
\dt

# 2. If the table appears with mixed casing like "Users" or "UserProfile":
# You must wrap the identifier in double quotes in your SQL query:
SELECT * FROM "Users";

# 3. Best Practice: Normalize quoted table names to standard lowercase:
ALTER TABLE "Users" RENAME TO users;
ALTER TABLE "UserProfile" RENAME TO user_profile;
# Now queries execute cleanly without requiring double quotes.

Step 3: Grant Schema USAGE & Table Permissions to Application Users

Ensure your application database user possesses both schema traversal rights and object-level permissions.

# Connect as superuser / database owner:
sudo -u postgres psql -d mydb

-- 1. Grant USAGE on the schema so the role can resolve relations:
GRANT USAGE ON SCHEMA app TO myuser;

-- 2. Grant read/write privileges on existing tables:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO myuser;

-- 3. Ensure future tables created by migrations automatically inherit privileges:
ALTER DEFAULT PRIVILEGES IN SCHEMA app 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;

-- 4. Grant sequence permissions for auto-incrementing serial/identity columns:
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO myuser;

Verification & Testing Steps

Confirm that your active connection resolves the relation and execute sample queries without errors.

# 1. Inspect the effective search_path of your connection:
SHOW search_path;
# Expected output: app, public

# 2. Verify relation visibility without schema prefixes:
psql -U myuser -d mydb -c "SELECT count(*) FROM users;"
# Expected output: count | 0 (or row count)

# 3. Inspect table metadata in psql:
\d users

Summary Comparison Table

Root Cause Category	Diagnostic Symptom	Primary Resolution	Prevention Best Practice
Custom Schema Omission	Table exists in \dt app.*	ALTER ROLE ... SET search_path TO app, public	Configure search_path in connection strings
Quoted Case Mismatch	Created with "Users"	Query with "Users" or rename to lowercase	Never use double-quoted mixed casing in DDL
Missing Schema Rights	Permission denied on schema	GRANT USAGE ON SCHEMA ... TO <user>	Configure default schema privileges
Wrong Target Database	Connected to postgres DB	Update connection URI database name	Specify explicit database in DATABASE_URL