This variable specifies the order in which schemas are searched when an object (table, data type, function, etc.) is referenced by a simple name with no schema specified. When there are objects of identical names in different schemas, the one found first in the search path is used. An object that is not in any of the schemas in the search path can only be referenced by specifying its containing schema with a qualified (dotted) name.
The value for
search_path must be a comma-separated list of schema names. Any name that is not an existing schema, or is a schema for which the user does not have
USAGE permission, is silently ignored.
If one of the list items is the special name
$user, then the schema having the name returned by SESSION_USER is substituted, if there is such a schema and the user has
USAGE permission for it. (If not,
$user is ignored.)
The system catalog schema,
pg_catalog, is always searched, whether it is mentioned in the path or not. If it is mentioned in the path then it will be searched in the specified order. If
pg_catalog is not in the path then it will be searched before searching any of the path items.
Likewise, the current session's temporary-table schema,
pg_temp_nnn, is always searched if it exists. It can be explicitly listed in the path by using the alias
pg_temppg_temp. If it is not listed in the path then it is searched first (even before
pg_catalog). However, the temporary schema is only searched for relation (table, view, sequence, etc) and data type names. It is never searched for function or operator names.
When objects are created without specifying a particular target schema, they will be placed in the first valid schema named in
search_path. An error is reported if the search path is empty.
The default value for this parameter is
"$user", public. This setting supports shared use of a database (where no users have private schemas, and all share use of
public), private per-user schemas, and combinations of these. Other effects can be obtained by altering the default search path setting, either globally or per-user.
For more information on schema handling, see ddl-schemas. In particular, the default configuration is suitable only when the database has a single user or a few mutually-trusting users.
The current effective value of the search path can be examined via the SQL function current_schemas (see functions-info). This is not quite the same as examining the value of
search_path, since current_schemas shows how the items appearing in
search_path were resolved.
- RE: "interesting" issue with restore from a pg_dump with a database-wide search_path
- unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)
- Add a semicolon to query related to search_path
- Re: "interesting" issue with restore from a pg_dump with a database-wide search_path
- Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)