Permanently Set Postgresql Schema Path

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?

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

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.

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.

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