sql - 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?

Answer 1

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