Postgres Relation Does Not Exist — Schema Fix

ERROR: relation "orders" does not exist. Every Postgres developer meets it — usually right after a deploy, a restore, or a switch to a new service. The table clearly exists: you can see it in the migration, your teammate queries it fine, and staging works. Yet your session insists it doesn't.

The confusion is that 'existing' isn't enough. Postgres resolves every unqualified table name through your search_path, folds unquoted names to lowercase, and scopes everything to the database you're connected to. Miss any of those and a perfectly healthy table becomes invisible to exactly one session. Teammates stay green because their roles, paths, and databases differ from yours in ways nobody wrote down.

This guide gives the resolution order: schema-qualify the name, inspect search_path, check case and quoting, confirm the database, and verify migrations. Five minutes of ordered checks replaces an hour of 'but it works on my machine.' The same order works whether you're debugging a failed deploy, a broken analyst query, or a restore that 'lost' tables — resolution failures all rhyme.

Read the Error: Which Relation, Which Schema

The message names the relation exactly as Postgres failed to resolve it — usually the bare name you wrote. Your first job is translating that name into candidate fully-qualified relations: query pg_tables for every schema holding a table of that name. One row in an unexpected schema means a qualification bug; zero rows means case, database, or migration causes. This single catalog query replaces all guessing about 'does the table exist'.

Note the catalog covers tables, not every relation kind: pg_class with relkind filters covers views (v), materialized views (m), and foreign tables (f) too. If pg_tables is empty but the app queries a view, check pg_views before concluding anything. And remember temporary tables: a pg_temp relation exists only inside the session that created it, so cross-session 'missing table' reports on temp names are expected behavior, not breakage.

Record the fully-qualified winner in the ticket. 'orders resolves to reporting.orders for the analytics role' is a diagnosis; 'table exists' is a shrug. Precision here shortens every downstream step. When pg_class shows multiple kinds sharing the name (a table and a view), the relkind column tells you which one your query would hit first.

-- Every schema holding a table of this name
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename = 'orders';

-- All relation kinds (views, matviews, foreign tables included)
SELECT nspname AS schema, relname, relkind
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relname = 'orders';

-- Session scope: database, schema, user in one row
SELECT current_database(), current_schema(), current_user;

search_path: The Invisible Default

search_path is a per-session ordered schema list, defaulting to "$user", public — meaning your own schema first, then public. Unqualified names resolve to the first match scanning left to right, so two roles can run identical SQL against different tables without either being 'wrong'. That flexibility is exactly why it bites: the setting is invisible in application code, settable per role, per database, and per session, in that precedence order.

Precedence matters when debugging. A session-level SET overrides the role default; the role default (ALTER ROLE ... SET) overrides the database default (ALTER DATABASE ... SET); all override postgresql.conf. Check all three when the path surprises you: SHOW search_path for the session, pg_roles.rolconfig for the role, and the database template for newcomers. Roles provisioned by copy-paste inherit yesterday's context — the analytics outage came from a warehouse path on an app role.

Prefer explicitness at the boundaries. Application SQL should schema-qualify table references in migrations and reporting queries (cheap, unambiguous), while interactive users keep a convenient path. Set role paths deliberately with private-first ordering — ALTER ROLE analytics SET search_path = analytics, public — so tenant-private tables shadow shared ones safely instead of accidentally.

-- What does THIS session search?
SHOW search_path;

-- Role default (survives reconnects) and database default
SELECT rolname, rolconfig FROM pg_roles WHERE rolname = current_user;

-- Session fix (immediate, this connection only)
SET search_path TO analytics, public;

-- Durable fix per role (new sessions inherit it)
ALTER ROLE analytics SET search_path = analytics, public;

-- Verify with a qualified probe
SELECT * FROM public.orders LIMIT 1;

Case-Folding: Why OrderItems Vanishes

Unquoted identifiers fold to lowercase — SELECT * FROM OrderItems really means orderitems. But double-quoted identifiers preserve case exactly, so CREATE TABLE "OrderItems" creates a name that only "OrderItems" (quoted, exact case) can ever reference. The unqualified, unquoted query then fails with 42P01 against a table that plainly exists in \dt output. ORMs and migration tools that quote inconsistently manufacture this trap on otherwise clean schemas.

Diagnose with case-insensitive matching: ILIKE against pg_tables reveals the stored casing in one row. If the stored name has capitals, prove the theory with a quoted probe — SELECT * FROM public."OrderItems" LIMIT 1 — which succeeds where the unquoted form fails. That pair (ILIKE find + quoted probe) closes the case without touching application code.

The durable fix is renaming to lowercase snake_case: ALTER TABLE ... RENAME TO order_items, plus matching renames for the columns and code references. Quoted mixed-case identifiers are a permanent tax — every future query, ORM mapping, and dump filter must quote exactly right. Pay the rename once instead of the quoting tax forever, and lint migrations to reject newly quoted identifiers.

-- Stored casing (ILIKE finds what = misses)
SELECT schemaname, tablename FROM pg_tables
WHERE tablename ILIKE 'orderitems';

-- Prove the folding trap: unquoted fails, quoted succeeds
-- SELECT * FROM public.OrderItems LIMIT 1;  -- 42P01 (folds to orderitems)
SELECT * FROM public."OrderItems" LIMIT 1;  -- works (exact case)

-- Durable fix: rename once, never quote again
ALTER TABLE public."OrderItems" RENAME TO order_items;

Wrong Database, Wrong Server

Relations live inside one database, and connections land wherever the connection string says — including the postgres maintenance database, a template, or last quarter's staging host. No search_path trick reaches across that boundary: 42P01 against a healthy table often means you're simply in the wrong building. Dockerized dev setups multiply this: localhost:5432 might be yesterday's container while the app targets a named host.

Verify from inside the failing session, not your terminal. SELECT current_database() plus the host from the app's config (or its startup log) names the actual building; compare against the working service's values. List candidates with \l in psql or SELECT datname FROM pg_database, and test explicitly with psql 'dbname=shop host=db-primary' before changing anything. Connection-string dbname typos (shop vs shops) are embarrassingly common and completely invisible to schema-level debugging.

Bake the database name into health checks now, not after the incident. A readiness probe that SELECTs a known table catches wrong-database wiring at deploy time, while a bare pg_isready only proves the server accepts sockets. The check that names the database beats the check that pings the port.

# From the shell: which databases exist here?
psql -h db-primary -U app -d postgres -c '\l'

# Connect to the EXACT database the app should use
psql 'dbname=shop host=db-primary user=app' -c 'SELECT current_database();'

# Prove the table is visible from that database
psql 'dbname=shop host=db-primary user=app' -c 'SELECT * FROM public.orders LIMIT 1;'

# Server alive at all? (proves sockets, not databases)
pg_isready -h db-primary -p 5432

Migrations Not Applied

When the catalog, case, and database all check out, the table may simply not exist yet in this environment. Deploys that run migrations asynchronously, blue-green cutovers that migrate the idle side late, restores that skipped the version table, and review apps sharing a host all produce genuine absence wearing a 42P01 costume. The migration-version table is the arbiter: no version row, no table — full stop.

Check per tool. Rails: schema_migrations; Goose: goose_db_version; Alembic: alembic_version; golang-migrate: schema_migrations; Django: django_migrations. Compare the latest applied version against the migration that creates the table and against what staging shows. A version present in staging but absent in prod names the exact unapplied file — run the migrator for that database (not just the deploy) and re-check.

Guard the ordering in the pipeline as a hard dependency, not a documented hope. Migrations must reach the database before app pods start serving — a post-start hook or init container beats 'the deploy ran, jobs will catch up.' And never let two services share one migrations table on one database unless they truly share a schema; interleaved version histories make 'migrated' meaningless per service.

-- Which migrations has THIS database actually applied?
SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 5;
-- Goose: SELECT version_id FROM goose_db_version ORDER BY id DESC LIMIT 5;
-- Alembic: SELECT version_num FROM alembic_version;
-- Django: SELECT app, name FROM django_migrations ORDER BY id DESC LIMIT 5;

-- Does the table exist here at all? (empty = never created)
SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'orders';

Prevention: Qualify, Lint, and Gate Deploys

Make 42P01 structurally unlikely. Schema-qualify table references in migrations, reporting queries, and cross-service SQL — public.orders never depends on anyone's path. Lint application SQL for unqualified references in review (a regex on migration diffs catches most), and reject quoted mixed-case identifiers at the migration gate so the folding trap can't be reintroduced by a helpful ORM.

Template role provisioning with explicit search_path per context: app roles get private-plus-public, warehouse roles get warehouse-first, humans get convenience paths. Review rolconfig quarterly the way you review grants — drift here is silent until the incident. Log SHOW search_path (plus database and user) at every service startup so the boot log answers the first diagnostic question before anyone asks it.

Gate deploys on a resolution probe: after migrations, run SELECT 1 FROM each critical table (qualified) from the app's own connection before shifting traffic. The probe fails on path, case, database, and migration causes alike — one check, four failure classes, zero dashboards down. Run it from CI with the exact role and connection string production uses, so the probe cannot pass on credentials the app lacks.

#!/bin/bash
# Deploy gate: resolve every critical table via the app's own connection
set -euo pipefail
CONN="dbname=shop host=db-primary user=app"
for T in public.orders public.users reporting.daily_revenue; do
  psql "$CONN" -c "SELECT 1 FROM $T LIMIT 1;" > /dev/null \
    && echo "RESOLVE-OK $T" || { echo "RESOLVE-FAIL $T"; exit 1; }
done
psql "$CONN" -c 'SHOW search_path;'
echo GATE-PASS