Permanently Set Postgresql Schema Path

Question

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?

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
– Way Too Simple, Commented Oct 24, 2019 at 18:44
There be dragons if you're using search_path: 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
– BigSmoke, Commented Nov 11, 2022 at 22:49

vitaly-t · Accepted Answer · 2018-04-18 00:15:25Z

237

(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:

When a schema name is not simple, it needs to be wrapped in double quotes.
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.

edited Apr 18, 2018 at 0:15

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

answered May 20, 2010 at 16:14

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jmoney38 Over a year ago

Also worth noting to explicitly NOT use quotes around the a,b,c enumeration. Hand-to-forehead for the past 15 minutes...

vitaly-t Over a year ago

@Jmoney38 Not to use single quotes, but double-quotes are required for non-simple schema names.

Ludovic Kuty Over a year ago

Yes, those are identifiers, not strings.

erik-stengel Over a year ago

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..

Cosmin Gruian Over a year ago

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 <data_base> set search_path TO a,b,c;

|

joshperry · Accepted Answer · 2022-10-09 03:31:20Z

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.

for those wondering from the psql command line you can list schemas by \dn
Need to disconnect the session and connect again for the settings to take effect.
oh bless you. came over to PG from SQL Server so this is all new (and strange) to me
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?

Chris Johnson · Accepted Answer · 2017-10-04 20:53:35Z

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.

Pluto · Accepted Answer · 2022-11-04 18:30:55Z

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

Collectives™ on Stack Overflow

Permanently Set Postgresql Schema Path

4 Answers 4

6 Comments

4 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

4 Comments

Comments

Comments

Linked

Related