USE

The USE statement sets the default database and/or schema for the current session. Objects referenced without a database.schema. qualifier resolve against this default and are created in it. current_database() and current_schema() report the active selection.

Examples

Select a database as the default; unqualified names then resolve inside it:

SQL

USE shop;
SELECT current_database();

 current_database
------------------
 shop

Select a schema within the current database:

SQL

USE analytics;
SELECT current_schema();

 current_schema
----------------
 analytics

Select a database and schema together — USE shop.analytics sets the database to shop and the schema to analytics:

SQL

USE shop.analytics;
SELECT current_database();
SELECT current_schema();

 current_database
------------------
 shop

 current_schema
----------------
 analytics

Relationship to search_path

USE is a convenience wrapper over the search_path session variable: it switches the current database and replaces search_path with the single schema you selected:

SQL

USE analytics;
SHOW search_path;

 search_path
-------------
 analytics

The "default schema" is just the head of search_path — what current_schema() returns and where unqualified objects are created. Selecting a database alone resets search_path to that database's own default schema; to keep several schemas on the path, set search_path directly instead of using USE.

The effect of each form on the session:

Statement	current_database()	current_schema()	search_path
(session default)	postgres	public	"$user", public
USE shop	shop	(shop's default)	(shop's default)
USE analytics	(unchanged)	analytics	analytics
USE shop.analytics	shop	analytics	analytics

Tip

A bare USE name is resolved schema-first. It looks for a schema with that name in the current database before it looks for a database. So when a database and a schema share a name, USE selects the schema and the database is shadowed.

To select the database instead, qualify it as USE <database>.<schema>.

See also

• CREATE DATABASE — create a database to USE
• SET — set search_path (and other variables) directly

Syntax

Syntax