Similar to Oracles alter session set current_schema=<schema_name>, in PostgreSQL,
search_path is a session-level setting that determines the order in which schemas are searched when you reference database objects
without a schema name.
It consists of a list of schema names. When you run a query like
SELECT * FROM mytable;
PostgreSQL checks each schema in the list — in order — to find
mytable.
Default value is:
"$user", public
This means:
- Look for a schema named after the current user.
- If not found or not accessible, try the
public schema.
How to view and set it
Check your current
search_path:
SHOW search_path;
Set it for the session:
SET search_path TO schema1, public;
or
SET search_path TO schema1, schema2, public;
The last example is important in cases where you logon to a database with a user with no matching schema.
Consider the following example:
psql -h server1.oric.no -U jim -d musicdb
I am logging onto the database "musicdb" with a user called "jim". By default, jim will have its own user followed by public, in his search path:
musicdb=> show search_path;
search_path
-----------------
"$user", public
(1 row)
I have already given user jim the privilges needed to "see" the objects created by schema "music" which exists in the database "musicdb".
For convenience, add schema "music" to the search_path:
musicdb=> set search_path to 'music','$user','public';
SET
musicdb=> show search_path;
search_path
-------------------------------
music, "$user", public
(1 row)
The "current_user" and "current_schema" functions will now return the actual user name, and the first match in the search_path, respectivly:
musicdb=> select current_user, current_schema;
current_user | current_schema
--------------+----------------
jim | music
(1 row)
Why is it important?
It controls
where PostgreSQL looks first for unqualified object names. It allows you to skip schema prefixes when working with other schemas' objects
