PostgreSQL version:

search_path

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.

Recommendations

Most DBAs either use the default or set search_path on a ROLE or database object basis. The one reason to set it in postgresql.conf is if you are taking the security step of removing the special "public" schema in order to lock down your database.

Comments

At postgresqlCO.NF (OnGres) we value your privacy and treat all data very seriously. We're fully GDPR compliant, and we continuously monitor and improve our data storage, retention and compliance mechanisms.

This web page does not, however, store any PII (Personally Identifiable Information). The only service that stores any data is Google Analytics, and we use it to gather analytics of the web page.

This website contains some data from the official documentation of the PostgreSQL.org project, and from Annotated.Conf, used with permission.

If you have any question or concern about our terms of service or privacy policy, please contact us at dataprotection _at_ ongres _dot_ com.

OK