Permanently Set Postgresql Schema Path
(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 yoursearch_path
.
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 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.