📋

Description

This error indicates that PostgreSQL cannot find a table referenced in your SQL query. It commonly occurs when the table name is misspelled, the table has not yet been created, or you are querying in the wrong schema or database.

💬

Error Message

undefined table

🔍

Known Causes

4 known causes

⚠️

Misspelled Table Name

A common cause is a simple typo in the table name within your SQL query, preventing the database from locating the specified table.

⚠️

Table Does Not Exist

The table you are trying to query has not been created in the current database or schema, or it was previously dropped.

⚠️

Incorrect Schema or Database

You might be connected to the wrong database or schema where the table resides, leading PostgreSQL to report it as undefined in the current context.

⚠️

Case Sensitivity Mismatch

If the table was created with mixed-case or quoted identifiers, but queried without matching case or quotes, PostgreSQL may not find it.

🛠️

Solutions

5 solutions available

1. Verify Table Name easy

Check spelling and case of table name

1

List all tables

\dt
-- or
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

2

Search for similar names

SELECT tablename FROM pg_tables
WHERE schemaname = 'public' AND tablename LIKE '%user%';

2. Check Schema easy

Table might be in different schema

1

Check current search_path

SHOW search_path;

2

List tables in all schemas

SELECT schemaname, tablename FROM pg_tables 
WHERE tablename = 'users';

3

Use fully qualified name

SELECT * FROM myschema.users;

4

Or add schema to search_path

SET search_path TO myschema, public;

3. Handle Case Sensitivity easy

PostgreSQL lowercases unquoted identifiers

1

Understand PostgreSQL naming

-- These are the same:
SELECT * FROM Users;
SELECT * FROM users;
SELECT * FROM USERS;

-- This is different (preserves case):
SELECT * FROM "Users";

2

Check exact table name

SELECT tablename FROM pg_tables WHERE tablename = 'Users';  -- Case-sensitive search

3

Use quotes for mixed-case names

SELECT * FROM "MyTable";

4. Create Missing Table easy

Create the table if it should exist

1

Create table

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

2

Run migrations if using framework

# Django
python manage.py migrate

# Rails
rails db:migrate

# Node.js (Sequelize)
npx sequelize-cli db:migrate

5. Check Database Connection easy

Verify you're connected to correct database

1

Check current database

SELECT current_database();

2

Connect to correct database

\c correct_database
-- or reconnect with psql:
psql -d correct_database -U username

🔗

Related Errors

5 related errors

PostgreSQL Error 42P01: Undefined Table