181

I need to set schema path in Postgres so that I don't every time specify schema dot table e.g. schema2.table. Set schema path:

SET SCHEMA PATH a,b,c

only seems to work for one query session on mac, after I close query window the path variable sets itself back to default.

How can I make it permanent?

vvvvv
  • 25,404
  • 19
  • 49
  • 81
Nakh
  • 1,821
  • 2
  • 12
  • 4
  • 2
    I think is SET search_path TO a, b,c; as the answer says and not SET SCHEMA PATH a,b,c; – Way Too Simple Oct 24 '19 at 18:44
  • There be dragons if you're using `search_path`: https://blog.bigsmoke.us/2022/11/11/postgresql-schema-search_path TL;DR: Don't use `search_path` in `SECURITY DEFINER` routines, make sure you control all schemas in the `search_path`, and also be careful when relying on the `search_path` within `SECURITY INVOKER` routines if these routines do things like generating a random password reset token. – BigSmoke Nov 11 '22 at 22:49

4 Answers4

230

(And if you have no admin access to the server)

ALTER ROLE <your_login_role> SET search_path TO a,b,c;

Two important things to know about:

  1. When a schema name is not simple, it needs to be wrapped in double quotes.
  2. The order in which you set default schemas a, b, c matters, as it is also the order in which the schemas will be looked up for tables. So if you have the same table name in more than one schema among the defaults, there will be no ambiguity, the server will always use the table from the first schema you specified for your search_path.
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
  • 29
    Also worth noting to explicitly NOT use quotes around the a,b,c enumeration. Hand-to-forehead for the past 15 minutes... – Jmoney38 Mar 05 '16 at 12:51
  • 7
    @Jmoney38 Not to use single quotes, but double-quotes are required for non-simple schema names. – vitaly-t Apr 17 '18 at 23:55
  • 1
    Yes, those are [identifiers](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS), not strings. – Ludovic Kuty Aug 27 '20 at 07:15
  • 1
    For me a reconnect to the database was necessary. ```SHOW SEARCH_PATH``` right after the ```ALTER ROLE``` seemed like it didn´t work at first.. – erik-stengel Apr 10 '22 at 17:53
  • I don't recommend using this command because it will modify the search_path for all databases that the role has access on. I would advise using ALTER DATABASE set search_path TO a,b,c; – Cosmin Gruian Oct 10 '22 at 11:42
  • Reconnect to psql is necessary as well – erik-stengel Mar 30 '23 at 05:36
181

You can set the default search_path at the database level:

ALTER DATABASE <database_name> SET search_path TO schema1,schema2;

Or at the user or role level:

ALTER ROLE <role_name> SET search_path TO schema1,schema2;

Or at the role+database level (thanks to Chris for pointing this out!):

ALTER ROLE <role_name> IN DATABASE <database_name> SET search_path TO schema1,schema2;

Or if you have a common default schema in all your databases you could set the system-wide default in the config file with the search_path option.

When a database is created it is created by default from a hidden "template" database named template1, you could alter that database to specify a new default search path for all databases created in the future. You could also create another template database and use CREATE DATABASE <database_name> TEMPLATE <template_name> to create your databases.

joshperry
  • 41,167
  • 16
  • 88
  • 103
  • 12
    for those wondering from the psql command line you can list schemas by \dn – BenKoshy Sep 30 '16 at 01:00
  • 8
    Need to disconnect the session and connect again for the settings to take effect. – isapir Dec 25 '17 at 16:44
  • oh bless you. came over to PG from SQL Server so this is all new (and strange) to me – Nate Anderson Sep 11 '19 at 02:25
  • The documentation has it as user_name or role_name. It does not work when I create as role_name (groupname). It gives me the error as ERROR: permission denied for schema public. I granted usage and default to public and yet the permissions for role does not work. Any ideas or help on this please? – user1456618 Nov 24 '20 at 17:13
45

Josh is correct but he left out one variation:

ALTER ROLE <role_name> IN DATABASE <db_name> SET search_path TO schema1,schema2;

Set the search path for the user, in one particular database.

Chris Johnson
  • 20,650
  • 6
  • 81
  • 80
1

If you're working exclusively with one database on a machine, you can save a default schema to ~/.psqlrc:

set search_path to a,b,c

More information on that here. If you wanted to store default connection information so you can simply run psql each time, you'd need to use environmental variables for that:

export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=database
export PGUSER=username
Pluto
  • 2,900
  • 27
  • 38