CREATE DATABASE usage instructions

You can create multiple databases in your PostgreSQL instance. When creating databases, you can specify a template library, and set different attributes such as character sets, localized COLLATE, and currencies for different databases.

This article provides examples to illustrate how to use the CREATE DATABASE command to set a template library and specify a character set, LC_COLLATE, and LC_CTYPE.

CREATE DATABASE syntax

Command:     CREATE DATABASE  
Description: create a new database  
Syntax:  
CREATE DATABASE name  
    [ [ WITH ] [ OWNER [=] user_name ]  
           [ TEMPLATE [=] template ]  
           [ ENCODING [=] encoding ]  
           [ LC_COLLATE [=] lc_collate ]  
           [ LC_CTYPE [=] lc_ctype ]  
           [ TABLESPACE [=] tablespace_name ]  
           [ CONNECTION LIMIT [=] connlimit ] ]

Clone a template library

Cloning a template library means creating a database from a specified template. The following example illustrates how to use the CREATE DATABASE command to create a database from a specified template.

Example

Clone a database named “test” to create another database named “test01”. The command is as follows.

test=> create database test01 with template test;  
CREATE DATABASE

Note:

• If no template is specified, the default template is template1.

• When a template library is used to create a database, it must not have any other user connections except the current connection. For example, if another user connects to the test database, an error similar to the following one may be returned.

    test=> create database test01 with template test;  
    ERROR:  source database "test" is being accessed by other users  
    DETAIL:  There is 1 other session using the database.

Specify the character set type

You can specify encoding when creating a database. The following example illustrates how to use the CREATE DATABASE command to create a database with a specified character set type.

Character set types supported by PostgreSQL

The following table lists the character set types supported by PostgreSQL. Server=Yes indicates that the character set can be used by the CREATE DATABASE command, and Server=No indicates that the character set can only be used on a client. For more information, see the PostgreSQL official document.

Example

Create a database with the UTF-8 character set. The command is as follows.

test=> create database test02 with encoding 'UTF-8';  
CREATE DATABASE

Note:

• The specified character set must be a superset of the template library’s character set; otherwise, an error is returned.

• The specified LC_COLLATE and LC_CTYPE must be compatible with the target character set. Otherwise, an error is returned.

• Error example: Assume that template1 is the default template library and its character set is UTF-8.

  test=> \l template1  
                                  List of databases  
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges     
  -----------+----------+----------+-------------+-------------+-----------------------  
  template1 | xxxxxxxx | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/xxxxxxxx          +  
           |          |          |             |             | xxxxxxxx=CTc/xxxxxxxx  
  (1 row)

  If you use template1 to create a database with the EUC_CN character set, the following error is returned:

  • The EUC_CN character set is incompatible with LC_COLLATE and LC_CTYPE of the template library.

    test=> create database test03 with encoding 'EUC_CN';  
    ERROR:  encoding "EUC_CN" does not match locale "zh_CN.UTF-8"  
    DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".

  • The EUC_CN character set is incompatible with the UTF-8 character set of the template library.

    test=> create database test03 with encoding 'EUC_CN' lc_collate='C' lc_ctype='C';  
    ERROR:  new encoding (EUC_CN) is incompatible with the encoding of the template database (UTF8)  
    HINT:  Use the same encoding as in the template database, or use template0 as template.

• Solution: Use template0 as the template library.

    create database test03 with encoding 'EUC_CN' template template0;

Set LC_COLLATE and LC_CTYPE

This example illustrates how to query the LC_COLLATE and LC_CTYPE supported by character sets, how to use the CREATE DATABASE command to specify LC_COLLATE and LC_CTYPE, and how to modify the LC_COLLATE and LC_CTYPE of an existing database.

Query the LC_COLLATE and LC_CTYPE supported by character sets

Query the pg_collation system table by running the following SQL statement to obtain the LC_COLLATE and LC_CTYPE supported by character sets.

test=> select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;

The returned results are as follows. When encoding is null, the collation supports all character sets.

  encoding  |       collname        |      collcollate      |       collctype         
------------+-----------------------+-----------------------+-----------------------  
            | default               |                       |   
            | C                     | C                     | C  
            | POSIX                 | POSIX                 | POSIX  
 UTF8       | aa_DJ                 | aa_DJ.utf8            | aa_DJ.utf8  
 LATIN1     | aa_DJ                 | aa_DJ                 | aa_DJ  
 LATIN1     | aa_DJ.iso88591        | aa_DJ.iso88591        | aa_DJ.iso88591  
 UTF8       | aa_DJ.utf8            | aa_DJ.utf8            | aa_DJ.utf8  
 UTF8       | aa_ER                 | aa_ER                 | aa_ER  
 UTF8       | aa_ER.utf8            | aa_ER.utf8            | aa_ER.utf8  
.......  
 EUC_CN     | zh_CN                 | zh_CN                 | zh_CN  
 UTF8       | zh_CN                 | zh_CN.utf8            | zh_CN.utf8  
 EUC_CN     | zh_CN.gb2312          | zh_CN.gb2312          | zh_CN.gb2312  
 UTF8       | zh_CN.utf8            | zh_CN.utf8            | zh_CN.utf8  
 UTF8       | zh_HK                 | zh_HK.utf8            | zh_HK.utf8  
 UTF8       | zh_HK.utf8            | zh_HK.utf8            | zh_HK.utf8  
 EUC_CN     | zh_SG                 | zh_SG                 | zh_SG  
 UTF8       | zh_SG                 | zh_SG.utf8            | zh_SG.utf8  
 EUC_CN     | zh_SG.gb2312          | zh_SG.gb2312          | zh_SG.gb2312  
 UTF8       | zh_SG.utf8            | zh_SG.utf8            | zh_SG.utf8  
 EUC_TW     | zh_TW                 | zh_TW.euctw           | zh_TW.euctw  
 UTF8       | zh_TW                 | zh_TW.utf8            | zh_TW.utf8  
 EUC_TW     | zh_TW.euctw           | zh_TW.euctw           | zh_TW.euctw  
 UTF8       | zh_TW.utf8            | zh_TW.utf8            | zh_TW.utf8  
 UTF8       | zu_ZA                 | zu_ZA.utf8            | zu_ZA.utf8  
 LATIN1     | zu_ZA                 | zu_ZA                 | zu_ZA  
 LATIN1     | zu_ZA.iso88591        | zu_ZA.iso88591        | zu_ZA.iso88591  
 UTF8       | zu_ZA.utf8            | zu_ZA.utf8            | zu_ZA.utf8  
(869 rows)

Specify LC_COLLATE and LC_CTYPE when creating a database

Example

Create a database with LC_COLLATE and LC_CTYPE set to zh_CN.utf8. The command is as follows.

test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
CREATE DATABASE

Note:

If the specified LC_COLLATE and LC_CTYPE are incompatible with the template library, the following error is returned.

test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
ERROR:  new collation (zh_CN.utf8) is incompatible with the collation of the template database (zh_CN.UTF-8)  
HINT:  Use the same collation as in the template database, or use template0 as template.

Two solutions are available:

• Use the compatible LC_COLLATE and LC_CTYPE. The command is as follows.

  test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.UTF-8' lc_ctype='zh_CN.UTF-8';  
  CREATE DATABASE

• Use template0 as the template library. The command is as follows.

  test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
  CREATE DATABASE

Modify the LC_COLLATE and LC_CTYPE of an existing database

Currently, the LC_COLLATE and LC_CTYPE of an existing database cannot be modified using the alter database command. However, you can create a database and then import the data that has been exported from the source database to the new database to make the modification.

Procedure

1. Create a database and specify LC_COLLATE and LC_CTYPE.

2. Use pg_dump or another client tool to logically export the data of the source database.

3. Use pg_restore or another client tool to import the exported data to the new database.

Reference document

PostgreSQL 9.6.2 Documentation - CREATE DATABASE