Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL cannot create a table because search_path has no valid schema

ErrorSQLSTATE 3F000 (invalid_schema_name)

Short answer

PostgreSQL creates an unqualified object in the first schema of search_path that exists and that you can use. If the path is empty, or lists only missing or inaccessible schemas, nothing qualifies. Show the path, then fix it, create the schema, grant USAGE, or qualify the name.

  • Applies to PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Show the effective path. Names that do not exist, or that you cannot use, are dropped from this list.

    SELECT current_schemas(false), current_user;
  2. 2Match what you see, then jump to its fix

    1. 1SHOW search_path returns nothing or an empty stringsearch_path is emptyFix
    2. 2The path names a schema (or "$user") that was never createdThe schemas in search_path do not existFix
    3. 3The schema exists in information_schema but current_schemas() omits itThe role has no USAGE on the schemaFix
    4. 4Every new session has the bad pathA role or database default sets the wrong pathFix

What does the error look like?

  • SQLSTATE 3F000 (invalid_schema_name)
  • no schema has been selected to create in

Cause 1 of 4

search_path is empty

1Check

An error is reported when the path is empty and an object is created without a schema.

SHOW search_path;

2Fix

Set a real path for the session, or qualify the name.

SET search_path TO public;
-- or
CREATE TABLE public.items (id int);

3Verify

The table is created.

SELECT to_regclass('public.items');

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 4

The schemas in search_path do not exist

1Check

A name that is not an existing schema is silently ignored, so a path of only missing names acts like an empty one.

SELECT schema_name FROM information_schema.schemata;

2Fix

Create the schema, then use it.

CREATE SCHEMA app;
SET search_path TO app, public;

3Verify

An unqualified CREATE TABLE lands in app.

CREATE TABLE items (id int);
SELECT to_regclass('app.items');

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 4

The role has no USAGE on the schema

1Check

A schema the user lacks USAGE on is ignored in the path, as if it did not exist.

SELECT current_schemas(false);

2Fix

Create a schema for the role, or have the owner grant USAGE and CREATE.

CREATE SCHEMA AUTHORIZATION app_user;

3Verify

Reconnect as the role and create a table.

CREATE TABLE t (id int);

Confirmed by postgresql.org

Did this fix it?

Cause 4 of 4

A role or database default sets the wrong path

1Check

ALTER ROLE ... SET applies at login, so each new session inherits it.

SELECT * FROM pg_db_role_setting;

2Fix

Reset or set it for the role; the change applies to new sessions only.

ALTER ROLE app_user SET search_path = app, public;
-- or: ALTER ROLE app_user RESET search_path;

3Verify

Reconnect and check.

SHOW search_path;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Schema-qualify table names in migrations.
  • Create schemas in the same migration step that uses them.
  • Set search_path per role.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.