Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

CREATE SCHEMA ​

Create a new schema in the current database.

CREATE SCHEMA name [ AUTHORIZATION user ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION user | CURRENT_USER | SESSION_USER [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS name [ AUTHORIZATION user ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user

Only sysadmin users, the database owner, and users who have been granted the CREATE privilege can create a schema. For granting CREATE privileges, see GRANT.

AUTHORIZATION user
The owner of the schema. If you do not enter a schema name, the user name becomes the name of the schema.
CURRENT_USER
See CURRENT_USER.
SESSION_USER
See SESSION_USER.
schema_element
Use a complete CREATE TABLE or CREATE VIEW statement to create a table or view that belongs to the new schema.
IF NOT EXISTS
Create the schema if it does not already exist. If it does exist, do not create it and do not return an error.

For example:

premdb=# create schema league;
CREATE SCHEMA
premdb=# create schema if not exists league;
CREATE SCHEMA

Create a schema named and owned by user bobr:

premdb=# create schema authorization bobr;
CREATE SCHEMA

Within a single CREATE SCHEMA statement, create a schema, a table, and a view:

premdb=# create schema league 
create table test(c1 int) 
create view testv as select * from test;
CREATE SCHEMA
premdb=# set schema 'league';
SET
premdb=# \d
       List of relations
 Schema | Name  | Type  |  Owner  
--------+-------+-------+---------
 league | test  | table | brumsby
 league | testv | view  | brumsby
(2 rows)

You can query tables across schemas in the same database. For example, the awayteam table in this query exists in both the bobr schema and the public schema:

premdb=# TABLE premdb.bobr.awayteam UNION TABLE premdb.public.awayteam ORDER BY 1;
 atid |          name           
------+-------------------------
   51 | Arsenal
   52 | Aston Villa
   53 | Barnsley
   54 | Birmingham City
   55 | Blackburn Rovers
...

Parent topic:SQL Commands