Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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?

1 Answer

0 votes
by (40.7k points)

If you don't have the admin access to the server, then use the below query:

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

Two important things to keep in mind are as follows

  • If the schema name is not simple, then it should 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. 

Therefore, if you have the same table name in more than one schema among the defaults, then there will be no ambiguity, the server will always use the table from the first schema as you have specified for your search_path.

Related questions

0 votes
1 answer
0 votes
2 answers
asked Dec 31, 2020 in SQL by Appu (6.1k points)
0 votes
1 answer
asked Dec 27, 2020 in SQL by Appu (6.1k points)
0 votes
1 answer
0 votes
1 answer

31k questions

32.4k answers

500 comments

693 users

Browse Categories

...