PostgreSQL | Schema | Create a Schema (CREATE SCHEMA)

Tags:

• PostgreSQL
• Database

This page explains how to create a new schema in PostgreSQL by using the CREATE SCHEMA command.

Create a New Schema

To create a schema, use the CREATE SCHEMA command. The syntax is as follows.

CREATE SCHEMA schema_name

Specify the schema name (schema_name) to create a new schema in the currently connected database. You can specify any schema name, but names that start with pg_ have special meaning and cannot be used.

To create a schema, the role executing the command must be a superuser or have the CREATE privilege in the database where the schema will be created.

–

Now try it in practice. Connect to the database where the schema will be created. This time, create a schema in the devkuma database.

postgres=# \c devkuma
접속정보: 데이터베이스="devkuma", 사용자="postgres".
devkuma=#

To check the schemas currently created in the devkuma database, run the psql meta-command \dn.

devkuma=# \dn
스키마(schema) 목록
  이름  |  소유주
--------+----------
 public | postgres
(1개 행)


devkuma=#

You can confirm that the default public schema has been created.

Now create a new schema. Run the following.

devkuma=# create schema myschema;
CREATE SCHEMA
devkuma=#

A new schema has been created. To confirm, run the \dn command again.

devkuma=# \dn
 스키마(schema) 목록
   이름   |  소유주
----------+----------
 myschema | postgres
 public   | postgres
(2개 행)


devkuma=#

You can confirm that the myschema schema has been created.

Specify the Owner of the Schema to Create

When a schema is created, the owner of the created schema is normally the role that created it, but you can create a schema with another role specified as the owner. The syntax is as follows.

CREATE SCHEMA schema_name AUTHORIZATION role_specification

Specify the schema name (schema_name) and the schema owner (role_specification) to create a new schema. To create a schema in this form, the role executing the command must be a direct or indirect member of the owning role, or be a superuser.

Now try it in practice. After connecting to the database where the schema will be created, run the following.

devkuma=# create schema orange_schema authorization orange;
CREATE SCHEMA
devkuma=# 

The orange_schema schema has been created with the orange role as its owner. To confirm, run the \dn command.

devkuma=# \dn
   스키마(schema) 목록
     이름      |  소유주
---------------+----------
 myschema      | postgres
 orange_schema | orange
 public        | postgres
(3개 행)


devkuma=#

In addition to the schemas that already existed, you can confirm that orange_schema, owned by the orange role, has been created.

Create Objects Such as Tables When Creating a New Schema

When creating a new schema, you can create objects such as tables or indexes in the schema at the same time. The syntax is as follows.

CREATE SCHEMA schema_name
  [ AUTHORIZATION role_specification ] 
  schema_element [ ... ]

Write the SQL commands to create at the same time in schema_element. The commands that can be written are CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER, and GRANT. When writing multiple commands, do not put a semicolon (;) at the end of each SQL command.

Now try it in practice. After connecting to the database where the schema will be created, run the following.

devkuma=# create schema testschema create table testtbl (id integer);
CREATE SCHEMA
devkuma=#

The testschema schema has been created, and the testtbl table has been created in that schema. To confirm, run the \dt command.

devkuma=# \dt testschema.*
         릴레이션(relation) 목록
   스키마   |  이름   |  종류  |  소유주
------------+---------+--------+----------
 testschema | testtbl | 테이블 | postgres
(1개 행)


devkuma=#

You can confirm that the testtbl table has been created in the testschema schema.

–

This page explained how to create a new schema by using the CREATE SCHEMA command.