CREATE SCHEMA

Description

Creates a schema. DATABASE is synonymous with SCHEMA.

Named objects are accessed either by "qualifying" their names with the schema name as a prefix, or by setting a search path that includes the desired schema. When creating named objects, you can also use the schema name as a prefix.

Optionally, CREATE SCHEMA can include sub-commands to create objects within the new schema. The sub-commands are treated essentially the same as separate commands issued after creating the schema. If the AUTHORIZATION clause is used, all the created objects are owned by this user.

Precautions

• Only a user with the CREATE permission on the current database can perform this operation.
• The owner of an object created by a system administrator in a schema with the same name as a common user is the common user, not the system administrator.
• templatem cannot be used as the schema name when creating a schema.

Syntax

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name [AUTHORIZATIO role_id]
    [[create_option] [,...]]

create_option: [DEFAULT] {
    CHARACTER SET [=] default_charset
  | CHAR SET [=] default_charset
  | CHARSET [=] default_charset
  | COLLATE [=] default_collation
}

Parameters

• schema_name

  Specifies the schema name.

  

  • The schema name must be unique in the current database.
  • The schema name cannot be the same as the initial username of the current database.
  • The schema name cannot start with pg_.
  • The schema name cannot start with gs_role_.

  Value range: a string complying with Identifier Description.

If objects in the schema on the current search path are with the same name, specify the schemas for different objects. You can run SHOW SEARCH_PATH to check the schemas on the current search path.

Examples

-- Create and switch to the test database.
m_db=# CREATE SCHEMA test1;
m_db=# USE test1;
-- Create the a table.
m_db=# CREATE TABLE a(id int);
m_db=# DROP TABLE a;
-- Drop the test1 schema.
m_db=# USE public;
m_db=# DROP SCHEMA test1;

Helpful Links

ALTER SCHEMA and DROP SCHEMA