kuda.ai

Postgres search_path

Created on September 23, 2025

postgres

In Postgres, your default search_path is public.

Say you have the following tables:

schema: public
  tables:
  - schema_migrations

schema: bellevue
  website:
  - tils
  - blogs

When you run \dt, you will only see schema_migrations, but not the other tables:

kuda_ai=# \dt
               List of relations
 Schema |       Name        | Type  |   Owner
--------+-------------------+-------+-----------
 public | schema_migrations | table | davidkuda
(1 row)

For you to be able to see the other tables, too, you have to use \dt website.*:

kuda_ai=# \dt website.*
               List of relations
 Schema  |        Name         | Type  | Owner
---------+---------------------+-------+-------
 website | bellevue_activities | table | dev
 website | blog_tags           | table | dev
 website | blogs               | table | dev
 website | content             | table | dev
 website | pages               | table | dev
 website | songs               | table | dev
 website | tags                | table | dev
 website | til                 | table | dev
(8 rows)

This is because of your search_path.

You can update your search path with a sql command. Here are a few examples:

SET search_path TO website;

Now, see the result:

kuda_ai=# set search_path to website;
SET

kuda_ai=# \dt
               List of relations
 Schema  |        Name         | Type  | Owner
---------+---------------------+-------+-------
 website | bellevue_activities | table | dev
 website | blog_tags           | table | dev
 website | blogs               | table | dev
 website | content             | table | dev
 website | pages               | table | dev
 website | songs               | table | dev
 website | tags                | table | dev
 website | til                 | table | dev
(8 rows)

To reset:

RESET search_path;

You can include multiple schemas in order of precedence:

SET search_path TO my_schema, public;

You can also set a default permanently in postgresql.conf or per-role:

ALTER ROLE david SET search_path TO website;

This is also available in transactions. (Be aware that it will stay for the lifetime of the connection.)

BEGIN;
SET search_path TO website;
SELECT * FROM tils;
COMMIT;

To auto-revert after commit, use set local search_path as in:

BEGIN;
SET LOCAL search_path TO website;
SELECT * FROM tils;
COMMIT;