Postgres 42P01: Relation Does Not Exist After CREATE TABLE

Cause #1: The search_path Doesn't Include the Schema You Created the Table In

This is the culprit in maybe 80% of the cases I've seen. You run CREATE TABLE my_table ... and it works. Then you immediately run SELECT * FROM my_table and get ERROR: 42P01: relation "my_table" does not exist. The table exists, but Postgres can't find it because you're not looking in the right schema.

By default, Postgres uses a search_path of "$user", public. If your connection user doesn't have a schema named after them, it falls back to public. But if you created the table in another schema — say you ran SET search_path TO app earlier, or you explicitly did CREATE TABLE app.my_table — then a bare SELECT won't see it.

The fix is simple. Either qualify the table name every time:

SELECT * FROM app.my_table;

Or set your search_path properly:

SET search_path TO app, public;

For a permanent fix, alter the role:

ALTER ROLE your_user SET search_path TO app, public;

Don't bother with pg_dump or restarting the server — it won't help. Check your search_path first.

Cause #2: You're Still in the Same Transaction and the Table Isn't Visible Yet

Here's the sneaky one. You're inside a transaction block (BEGIN), you create a table, then you try to query it. In Postgres, a table created in a transaction is not visible to queries within the same transaction until you commit. Wait — that's not exactly true. Actually, it's the opposite. The table is visible to the session that created it, even before commit. But if you're using a connection pool with prepared statements or you have multiple sessions, you can run into visibility issues.

Let me clarify. If you do this in psql:

BEGIN;
CREATE TABLE foo (id int);
SELECT * FROM foo; -- works fine
COMMIT;

That works. The problem appears when you have a connection pooler like PgBouncer in transaction mode, or you're using an ORM that opens a new connection after the DDL. The new connection doesn't see the uncommitted table. So you get a 42P01 even though the CREATE TABLE just succeeded.

The fix is to commit the transaction before querying from another session. If you're using an ORM, make sure DDL statements are in their own transaction, or use AUTOCOMMIT for schema changes. Don't rely on cross-session visibility — it doesn't exist until commit.

Cause #3: Quoted Identifiers and Case-Sensitivity Gotcha

This one bites people who come from MySQL or SQL Server. In Postgres, unquoted identifiers are folded to lowercase. If you create a table with double quotes like CREATE TABLE "MyTable" ..., then SELECT * FROM MyTable will fail because Postgres treats MyTable as mytable and that doesn't match "MyTable".

You'll see the error message showing the relation name exactly as you typed it, but with quotes. Check if the error says relation "MyTable" does not exist — that's a dead giveaway.

The fix is to either drop the quotes and use lowercase:

CREATE TABLE mytable (id int);
SELECT * FROM mytable; -- works

Or consistently quote the table name every time:

SELECT * FROM "MyTable";

My advice: skip the quoted identifiers entirely. They cause more pain than they're worth. Use lowercase with underscores, like my_table. That's the idiomatic way.

Quick-Reference Summary Table

Remember, the error code 42P01 is just Postgres saying "I can't find this relation." It's not a data corruption or a hardware issue — it's almost always a logic problem in your code or session settings. Start with the search_path, then check your transaction boundaries, then look at how you're quoting names. You'll fix it in minutes.