search_path - Amazon Redshift

search_path

Values (default in bold)

'$user', public, schema_names

A comma-separated list of existing schema names. If '$user' is present, then the schema having the same name as SESSION_USER is substituted, otherwise it is ignored.

Description

Specifies the order in which schemas are searched when an object (such as a table or a function) is referenced by a simple name with no schema component:

  • Search paths aren't supported with external schemas and external tables. External tables must be explicitly qualified by an external schema.

  • When objects are created without a specific target schema, they are placed in the first schema listed in the search path. If the search path is empty, the system returns an error.

  • When objects with identical names exist in different schemas, the one found first in the search path is used.

  • An object that isn't in any of the schemas in the search path can only be referenced by specifying its containing schema with a qualified (dotted) name.

  • The system catalog schema, pg_catalog, is always searched. If it is mentioned in the path, it is searched in the specified order. If not, it is searched before any of the path items.

  • 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_temp. If it is not listed in the path, it is searched first (even before pg_catalog). However, the temporary schema is only searched for relation names (tables, views). It is not searched for function names.

Example

The following example creates the schema ENTERPRISE and sets the search_path to the new schema.

create schema enterprise; set search_path to enterprise; show search_path; search_path ------------- enterprise (1 row)

The following example adds the schema ENTERPRISE to the default search_path.

set search_path to '$user', public, enterprise; show search_path; search_path ----------------------------- "$user", public, enterprise (1 row)

The following example adds the table FRONTIER to the schema ENTERPRISE.

create table enterprise.frontier (c1 int);

When the table PUBLIC.FRONTIER is created in the same database, and the user does not specify the schema name in a query, PUBLIC.FRONTIER takes precedence over ENTERPRISE.FRONTIER.

create table public.frontier(c1 int); insert into enterprise.frontier values(1); select * from frontier; frontier ---- (0 rows) select * from enterprise.frontier; c1 ---- 1 (1 row)