This error occurs when PostgreSQL cannot find a table, view, or other relation with the specified name. The most common causes are case sensitivity issues, schema mismatches, table not being created, or incorrect connection to the database. Verify the table exists, check exact naming and case sensitivity, specify the correct schema, and ensure you're querying the right database.
PostgreSQL error 42P01 (undefined_table) occurs when you attempt to query a relation (table, view, index, sequence, or other object) that PostgreSQL cannot locate. The database searches for the relation in the current search_path (typically just the public schema by default). If the relation isn't found there, PostgreSQL raises this error and stops query execution. Common scenarios: - The table exists but in a different schema - Case sensitivity mismatch (PostgreSQL treats unquoted names as lowercase) - The table was never created or was dropped - Connected to the wrong database instance - User lacks permissions to see the table - ORM or application using wrong schema configuration
First, confirm you're connected to the right database and the table exists:
# Connect to PostgreSQL
psql -U username -d database_name -h localhost
# List all tables in current schema
\dt
# Or show tables in a specific schema
\dt schema_name.*If the table isn't listed, it either doesn't exist or is in a different schema.
Alternatively, query the information schema:
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;If querying a non-public schema:
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'my_schema'
ORDER BY table_name;PostgreSQL is case-insensitive for unquoted identifiers (they're folded to lowercase) but case-sensitive for quoted identifiers.
Find the exact table name:
-- Find tables matching a pattern (case-insensitive)
SELECT schemaname, tablename FROM pg_tables
WHERE tablename ILIKE 'users%';Example output:
schemaname | tablename
------------+-----------
public | users
public | users_auditThen use the exact name and case:
-- If created without quotes (case-insensitive)
CREATE TABLE users (...);
SELECT * FROM users; -- works
SELECT * FROM USERS; -- also works
SELECT * FROM Users; -- also works
-- If created WITH quotes (case-sensitive)
CREATE TABLE "Users" (...);
SELECT * FROM "Users"; -- must match exact case
SELECT * FROM "users"; -- fails with 42P01If the table exists in a non-public schema, reference it with the schema name:
-- Query fails if table is in app_schema, not public
SELECT * FROM orders;
-- Correct: fully qualify the name
SELECT * FROM app_schema.orders;Permanently set search_path for your session:
-- View current search_path
SHOW search_path;
-- Set it for this session
SET search_path TO app_schema, public;
-- Now unqualified names search in app_schema first
SELECT * FROM orders; -- finds app_schema.ordersOr set it for your user permanently:
ALTER ROLE your_user SET search_path TO app_schema, public;Then reconnect and the setting applies.
Ensure you're connecting to the correct database and host:
# Check current connection in psql
\c
# Output shows:
# You are connected to database "mydb" as user "myuser" on host "localhost"
# Connect to a different database
\c different_database
# Or from command line
psql -U myuser -d correct_database -h 127.0.0.1 -p 5432For application code, verify connection string:
// Node.js with pg library
const client = new Client({
host: 'localhost',
port: 5432,
database: 'correct_database', // Verify this
user: 'myuser',
password: 'password',
});
// Prisma
// DATABASE_URL=postgresql://user:password@localhost:5432/correct_databaseCommon mistakes:
- Using localhost instead of 127.0.0.1
- Wrong port number
- Wrong database name
- Wrong username/password (causes connection error, not 42P01)
If the table exists but you can't access it, check permissions:
-- Check what permissions your user has on the table
\dp table_name
-- Grant SELECT permission
GRANT SELECT ON table_name TO your_user;
-- Grant all permissions
GRANT ALL PRIVILEGES ON table_name TO your_user;
-- If table is in a non-public schema, also grant schema usage
GRANT USAGE ON SCHEMA schema_name TO your_user;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO your_user;View all permissions on a table:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'table_name';If using Prisma, SQLAlchemy, or other ORMs, check schema settings:
Prisma:
// .env or .env.local
DATABASE_URL=postgresql://user:password@localhost/db?schema=public
// prisma/schema.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
// schema defaults to "public" but can be overridden
}SQLAlchemy (Python):
# If using non-public schema
engine = create_engine(
'postgresql://user:password@localhost/db',
connect_args={'options': '-c search_path=my_schema'}
)TypeORM (Node.js):
createConnection({
type: 'postgres',
host: 'localhost',
database: 'mydb',
schema: 'my_schema', // Specify if not public
entities: [],
});Verify the schema name in your ORM configuration matches where the table actually exists.
If the table genuinely doesn't exist, create it:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Or with explicit schema:
CREATE TABLE app_schema.users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL
);If using Prisma migrations:
# Generate migration based on schema.prisma
npx prisma migrate dev --name add_users_table
# Apply pending migrations
npx prisma migrate deployIf using Alembic (SQLAlchemy):
# Create and run migration
alembic upgrade headUnderstanding PostgreSQL search_path:
The search_path is an ordered list of schemas PostgreSQL searches when you reference an unqualified object name. Default is "$user", public - meaning it searches your username schema first, then public.
Example:
SHOW search_path;
-- Returns: "$user", publicIf you have a table "orders" in both app_schema and public, and search_path is "app_schema, public", it will find app_schema.orders first.
Migration Timing Issues:
When using migrations (Prisma, Alembic, Flyway), migrations must run before your application queries the tables. Common issues:
- Migration files in wrong order (filename-based ordering)
- Connection pool getting stale connections before migration completes
- Different environments (dev/staging/prod) with different migration states
ORM Lazy Loading:
Some ORMs lazy-load tables on first query. If the table doesn't exist yet, you get 42P01. Ensure migrations run before ORM initialization.
Docker/Container Issues:
If using PostgreSQL in Docker:
- Init scripts must create tables before app starts
- Multiple containers accessing same DB: ensure migrations run once at startup
- Volume mounting: schema persists but verify table definitions match code
Troubleshooting Checklist:
1. \dt schema_name.* shows the table? If not, it doesn't exist
2. Exact table name and case match the create statement?
3. SHOW search_path includes the schema where table was created?
4. SELECT * FROM pg_tables WHERE schemaname='schema_name' finds it?
5. User can SELECT from other tables? (permissions issue vs missing table)
6. \c confirms correct database?
7. SELECT version() shows you're on the expected PostgreSQL version?
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL