search_path - Amazon Redshift
Values (default in bold)DescriptionExample
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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)