What is the built-in postgresql variable “search_path”?
The PostgreSQL search_path variable allows you to control what order schemas are searched and which schemas do not require schema qualification to use tables/views/functions in the schema. For example, if I simply type: create table junk as select current_user as curusr; What database and schema will this table be created in.
First of all how do I know which database and schema I am currently using?
select current_database, current_schema;
Then, I can see the current search_path with:
show search_path ;
And you can set the search_path for the current session with:
set search_path = "$user", public, postgis;
You can permanently set the search_path for a given database with:
alter database mikes_db set search_path = "$user", public, postgis ;
And finally you can permanently set the search_path for a given role (user) with:
alter role mike set search_path = "$user", public, postgis ;
-- or maybe something like this:
alter role mike set search_path = mikes_schema;