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?
This question is related to
sql
postgresql
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.
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.
Source: Stackoverflow.com