kuda.ai

Postgres search_path

Created on September 23, 2025 Updated on November 14, 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;

priority

• current session
• The role
• The database
• The cluster default (“$user”, public”) (in postgresql.conf)

-- session:
set search_path = webapp, public;

-- role:
ALTER ROLE davidkuda
SET search_path = webapp, public;

-- database:
ALTER DATABASE kudaai
SET search_path = webapp, public;

examine existing search_path

select datname, setconfig
from pg_db_role_setting s
left join pg_database d on d.oid = s.setdatabase;

-[ RECORD 1 ]-------------------------------
datname   |
setconfig | {search_path=bellevue}
-[ RECORD 2 ]-------------------------------
datname   | kuda_ai
setconfig | {"search_path=bellevue, public"}

select
    r.rolname,
    coalesce(s.setconfig::text, '∅') AS search_path_default
FROM pg_roles r
LEFT JOIN pg_db_role_setting s
    ON s.setrole = r.oid
   AND s.setdatabase = 0
ORDER BY r.rolname;

           rolname           |  search_path_default
-----------------------------+------------------------
 app                         | {search_path=bellevue}
 davidkuda                   | ∅
 dev                         | ∅
 developer                   | ∅
 kuda_ai                     | ∅
 pg_checkpoint               | ∅
 pg_create_subscription      | ∅
 pg_database_owner           | ∅
 pg_execute_server_program   | ∅
 pg_monitor                  | ∅
 pg_read_all_data            | ∅
 pg_read_all_settings        | ∅
 pg_read_all_stats           | ∅
 pg_read_server_files        | ∅
 pg_signal_backend           | ∅
 pg_stat_scan_tables         | ∅
 pg_use_reserved_connections | ∅
 pg_write_all_data           | ∅
 pg_write_server_files       | ∅
(19 rows)