# PostgreSQL error 42P01: relation does not exist

The table usually exists. PostgreSQL looked for it along search_path and did not find it there, or the name was folded to lower case and no longer matches a table created with capitals. Both cases report the same error as a genuinely missing table.

> Confidence: high · Verified: 2026-08-08 · Status: fresh · Source: https://knowbase.sh/k/postgres-42p01-relation-does-not-exist

## Error signature

```
ERROR: relation "x" does not exist
```

Codes: 42P01, undefined_table

## Problem

A query fails saying the relation does not exist, but the table is visibly there in psql or a GUI client. The same SQL works for one user and fails for another, or works in psql and fails from the application, which makes it look like a permissions or connection problem rather than a name resolution one.

## Root cause

- **The table is in a schema that is not on search_path** _(primary)_
  - Unqualified names are resolved by walking search_path and taking the first match. A table in a schema outside that list is invisible even though it exists in the same database — and the error is identical to it not existing at all.
  - How to tell: SHOW search_path does not list the schema, while the table appears in information_schema.tables under a different schema name
- **The identifier was created quoted with capitals** _(primary)_
  - Unquoted names are folded to lower case. A table created as "MyTable" can only be referenced as "MyTable" with quotes; writing MyTable becomes mytable and does not match. ORMs that quote on create but not on query cause exactly this.
  - How to tell: The table name in information_schema.tables contains capitals, and the query succeeds when the name is double-quoted
- **The connection is to the wrong database** _(common)_
  - search_path is per-session and schemas are per-database. A connection string pointing at the default database rather than the application's will report every table missing.
  - How to tell: SELECT current_database(), current_schema() returns something other than what the application expects
- **The migration has not run on this environment** _(common)_
  - The table exists in development and not in the target. Straightforward, but worth excluding before chasing search_path — it is the only cause where the table is genuinely absent.
  - How to tell: The table is absent from information_schema.tables in every schema, not merely outside search_path
- **The object exists but is not the kind being used** _(edge)_
  - Referencing a sequence, view or type where a table is expected, or querying a composite type by name. The name resolves to something, just not to a relation usable in that position.
  - How to tell: pg_class lists the name with a relkind other than r or p, or the object is a type rather than a relation
- **A temporary table from another session** _(edge)_
  - Temporary tables live in a per-session schema and vanish with the connection. With a pooled connection the table may have been created on a different physical session than the one now querying.
  - How to tell: The table was created as TEMPORARY and the application uses a connection pool

## Solution

1. Ask the database where the table actually is, across every schema. This single query separates the four common causes from each other.

```bash
psql -c "SELECT table_schema, table_name FROM information_schema.tables WHERE table_name ILIKE '%your_table%';"
```

   Note: ILIKE is deliberate — a case-insensitive match reveals a name created with capitals, which an exact match would hide.
2. Check what the failing session can see, since search_path is per-session and may differ from psql's.

```bash
psql -c 'SELECT current_database(), current_schema(), current_user;' -c 'SHOW search_path;'
```

3. If it is a schema problem, prefer qualifying the name over changing the path. A qualified reference resolves the same way for every user and role.

```sql
-- ✅ unambiguous regardless of search_path
SELECT * FROM analytics.events;

-- session-scoped alternative
SET search_path TO analytics, public;

-- persistent, per role
ALTER ROLE app_user SET search_path TO analytics, public;
```

4. If it is a case problem, decide on one convention and hold it. Lower case unquoted is the path of least resistance in PostgreSQL; the alternative is quoting the name everywhere, forever.

```sql
-- created quoted, so it must always be quoted
CREATE TABLE "MyTable" (id int);
SELECT * FROM MyTable;     -- 🔴 folded to mytable, fails
SELECT * FROM "MyTable";   -- ✅

-- rename to the convention instead of quoting forever
ALTER TABLE "MyTable" RENAME TO my_table;
```

5. Verify the migration actually ran against this database before assuming name resolution is at fault.

```bash
psql -c "SELECT * FROM schema_migrations ORDER BY version DESC LIMIT 5;"
```

6. For pooled connections, never rely on a temporary table surviving between statements — create it and use it inside one transaction, or use an unlogged table with an explicit lifetime.

**Verify:** The failing query runs from the application's own connection, and information_schema.tables confirms the schema and exact spelling the query uses.

**If that fails:** Where an ORM generates unqualified names you cannot control, set search_path on the role rather than in application code, so every connection that role opens resolves names the same way regardless of where the query is built.

## Applies to

- PostgreSQL: 9.0 and later — Unquoted identifiers fold to lower case, which is incompatible with the SQL standard's upper-case folding — portable code should quote consistently or never.
- search_path: all versions — Defaults to "$user", public — so a custom schema is invisible until added.
- Platforms: self-hosted, managed PostgreSQL

## Not applicable to

- Permission errors (42501), where the relation is found but access is denied
- SQLSTATE 42703 undefined_column, which resolves the table but not a column in it
- MySQL error 1146 table doesn't exist, which has no search_path equivalent
- Connection failures, which never reach name resolution at all

## Evidence

1. [PostgreSQL — The Schema Search Path](https://www.postgresql.org/docs/current/ddl-schemas.html) — The PostgreSQL Global Development Group (official-docs), read 2026-08-08
   Supports: That unqualified names resolve along search_path, that the first match wins, and that no match produces an error even when the table exists in another schema of the same database — the exact behaviour behind the primary cause.
   > If there is no match in the search path, an error is reported, even if matching table names exist in other schemas in the database.
2. [PostgreSQL — Identifiers and Key Words](https://www.postgresql.org/docs/current/sql-syntax-lexical.html) — The PostgreSQL Global Development Group (official-docs), read 2026-08-08
   Supports: That quoting makes an identifier case-sensitive while unquoted names fold to lower case, so a table created as "MyTable" cannot be reached by writing MyTable.
   > Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case.
3. [PostgreSQL Error Codes (Appendix A)](https://www.postgresql.org/docs/current/errcodes-appendix.html) — The PostgreSQL Global Development Group (official-docs), read 2026-08-08
   Supports: That 42P01 is undefined_table in class 42, Syntax Error or Access Rule Violation — a name-resolution class rather than a missing-object one.
   > undefined_table

## Confidence

high — The search_path resolution rule and the case-folding rule are quoted verbatim from PostgreSQL's own reference, and they account for the two primary causes. The pooled temporary-table case follows from documented temporary-schema behaviour rather than from a sentence about this error, and the diagnostic queries are standard practice rather than documented procedure.

---

Retrieved from https://knowbase.sh/k/postgres-42p01-relation-does-not-exist · knowbase · CC-BY-4.0