PostgreSQL | Database | Create a Database (CREATE DATABASE)

Tags:

• PostgreSQL
• Database

This page explains how to create a new PostgreSQL database by using the CREATE DATABASE command.

Create a New Database

To create a database, use the CREATE DATABASE command. The syntax is as follows.

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

Several options are available, but the basic syntax is as follows.

CREATE DATABASE name

Specify the database name (name) to create a new database. If no template is specified explicitly, the database is created by copying the template1 template database.

To create a database, the role executing the command must be a superuser or have the CREATEDB privilege.

Now try it in practice. Run the following from psql.

C:\Users\kimkc>psql -U postgres
postgres 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

postgres=# create database devkuma;
CREATE DATABASE
postgres=#

The database has been created.

Next, check the list of created databases.

postgres=# \l
                                      데이터베이스 목록
   이름    |  소유주  | 인코딩 |     Collate      |      Ctype       |      액세스 권한
-----------+----------+--------+------------------+------------------+-----------------------
 devkuma   | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |                        <<<< newly created
 postgres  | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
 sample    | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
 template0 | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |        |                  |                  | postgres=CTc/postgres
 template1 | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |        |                  |                  | postgres=CTc/postgres
(5개 행)


postgres=#

Create a Database with a Character Set and Collation

When creating a database, the character set (ENCODING), string sort order (LC_COLLATE), and character classification (LC_CTYPE) use the defaults if they are not specified explicitly. However, these settings cannot be changed after the database is created, so you need to be careful.

To create a database with an explicit character set, use the following syntax.

CREATE DATABASE name
  ENCODING encoding
  LC_COLLATE lc_collate
  LC_CTYPE lc_ctype

If the locale settings (LC_COLLATE and LC_CTYPE) differ from those of the default template1 template, you must explicitly use template0 as the template.

CREATE DATABASE name
  TEMPLATE template0
  ENCODING encoding
  LC_COLLATE lc_collate
  LC_CTYPE lc_ctype

–

Now try it in practice. This time, create a database with UTF8 as the character set and Korean_Korea.949 as the string sort order and character classification.

postgres=# create database devkuma2
postgres-#   template template0
postgres-#   encoding UTF8
postgres-#   lc_collate 'Korean_Korea.949'
postgres-#   lc_ctype 'Korean_Korea.949';
CREATE DATABASE
postgres=#	

A new database has been created with the character set, string sort order, and character classification specified.

Now list the created databases.

postgres=# \l
                                      데이터베이스 목록
   이름    |  소유주  | 인코딩 |     Collate      |      Ctype       |      액세스 권한
-----------+----------+--------+------------------+------------------+-----------------------
 devkuma   | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
 devkuma2  | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |                        <<< newly created
 postgres  | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
 sample    | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
 template0 | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |        |                  |                  | postgres=CTc/postgres
 template1 | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |        |                  |                  | postgres=CTc/postgres
(6개 행)


postgres=#

You can confirm that the newly created devkuma2 database exists and that its character set, string sort order, and character classification have been specified.

–

This page explained how to create a database by using the CREATE DATABASE command.