CURRENT_SCHEMA ​

Return the name of the first schema in the search path (or a null value if the search path is empty).

CURRENT_SCHEMA[()]

The parentheses are optional. For example:

yellowbrick=# SET SCHEMA 'stage';
SET

yellowbrick=# SELECT CURRENT_SCHEMA();
current_schema
----------------
stage
(1 row)

See also CURRENT_SCHEMAS().

SEARCH_PATH ​

The search_path configuration parameter defines the visibility of schemas to users.

yellowbrick=# set search_path to bobr,sys;
SET

yellowbrick=# show search_path;
 search_path
-------------
bobr, sys
(1 row)

Alternatively, you can use a set schema command with single quotes around the schema name to change the current schema.

yellowbrick=# set schema 'bobr';
SET

Note: set schema 'value' is equivalent to set search_path to 'value', but you can only specify one schema in the set schema syntax.

The search_path parameter additionally allows you to control which schemas do not require schema qualification when users access tables, views, and other objects.

A qualified schema name consists of the schema name and table name separated by dots. You may also include the database name before the schema name.

database.schema.table

New objects that are not schema-qualified, consisting of just the table name, are always created in the first schema of the search path. To access any object that is not in a schema listed in the search path, you must provide the schema name.

$user ​

The first schema on the search path is the current schema, which is the default location for creating new objects. When running the show command, the first element specifies that a schema with the same name as the current user is to be searched. The element is ignored if the schema does not exist. “$user” is the default placeholder that resolves to the current user’s name schema. The second element of the show command return refers to the public schema.

yellowbrick=# show search_path;
search_path
----------------
"$user", public
(1 row)

The result of this show command means:

• The user exists with the ability to create tables.
• The user's named schema exists, and the user owns it.
• The user schema is listed first in the user's search path, so new tables are automatically created and searched there first.

Parent topic:System Functions