edit-icon download-icon

CREATE DATABASE usage instructions

Last Updated: Feb 06, 2018

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

  1. Command: CREATE DATABASE
  2. Description: create a new database
  3. Syntax:
  4. CREATE DATABASE name
  5. [ [ WITH ] [ OWNER [=] user_name ]
  6. [ TEMPLATE [=] template ]
  7. [ ENCODING [=] encoding ]
  8. [ LC_COLLATE [=] lc_collate ]
  9. [ LC_CTYPE [=] lc_ctype ]
  10. [ TABLESPACE [=] tablespace_name ]
  11. [ 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.

  1. test=> create database test01 with template test;
  2. 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.

    1. test=> create database test01 with template test;
    2. ERROR: source database "test" is being accessed by other users
    3. 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.

Name Description Language Server? Bytes/Char Aliases
BIG5 Big Five Traditional Chinese No 1-2 WIN950, Windows950
EUC_CN Extended UNIX Code-CN Simplified Chinese Yes 1-3 -
EUC_JP Extended UNIX Code-JP Japanese Yes 1-3 -
EUC_JIS_2004 Extended UNIX Code-JP, JIS X 0213 Japanese Yes 1-3 -
EUC_KR Extended UNIX Code-KR Korean Yes 1-3 -
EUC_TW Extended UNIX Code-TW Traditional Chinese, Taiwanese Yes 1-3 -
GB18030 National Standard Chinese No 1-4 -
GBK Extended National Standard Simplified Chinese No 1-2 WIN936, Windows936
ISO_8859_5 ISO 8859-5, ECMA 113 Latin/Cyrillic Yes 1 -
ISO_8859_6 ISO 8859-6, ECMA 114 Latin/Arabic Yes 1 -
ISO_8859_7 ISO 8859-7, ECMA 118 Latin/Greek Yes 1 -
ISO_8859_8 ISO 8859-8, ECMA 121 Latin/Hebrew Yes 1 -
JOHAB JOHAB Korean (Hangul) No 1-3 -
KOI8R KOI8-R Cyrillic (Russian) Yes 1 KOI8
KOI8U KOI8-U Cyrillic (Ukrainian) Yes 1 -
LATIN1 ISO 8859-1, ECMA 94 Western European Yes 1 ISO88591
LATIN2 ISO 8859-2, ECMA 94 Central European Yes 1 ISO88592
LATIN3 ISO 8859-3, ECMA 94 South European Yes 1 ISO88593
LATIN4 ISO 8859-4, ECMA 94 North European Yes 1 ISO88594
LATIN5 ISO 8859-9, ECMA 128 Turkish Yes 1 ISO88599
LATIN6 ISO 8859-10, ECMA 144 Nordic Yes 1 ISO885910
LATIN7 ISO 8859-13 Baltic Yes 1 ISO885913
LATIN8 ISO 8859-14 Celtic Yes 1 ISO885914
LATIN9 ISO 8859-15 LATIN1 with Euro and accents Yes 1 ISO885915
LATIN10 ISO 8859-16, ASRO SR 14111 Romanian Yes 1 ISO885916
MULE_INTERNAL Mule internal code Multilingual Emacs Yes 1-4 -
SJIS Shift JIS Japanese No 1-2 Mskanji, ShiftJIS, WIN932, Windows932
SHIFT_JIS_2004 Shift JIS, JIS X 0213 Japanese No 1-2 -
SQL_ASCII unspecified (see text) any Yes 1 -
UHC Unified Hangul Code Korean No 1-2 WIN949, Windows949
UTF8 Unicode, 8-bit all Yes 1-4 Unicode
WIN866 Windows CP866 Cyrillic Yes 1 ALT
WIN874 Windows CP874 Thai Yes 1 -
WIN1250 Windows CP1250 Central European Yes 1 -
WIN1251 Windows CP1251 Cyrillic Yes 1 WIN
WIN1252 Windows CP1252 Western European Yes 1 -
WIN1253 Windows CP1253 Greek Yes 1 -
WIN1254 Windows CP1254 Turkish Yes 1 -
WIN1255 Windows CP1255 Hebrew Yes 1 -
WIN1256 Windows CP1256 Arabic Yes 1 -
WIN1257 Windows CP1257 Baltic Yes 1 -
WIN1258 Windows CP1258 Vietnamese Yes 1 ABC, TCVN, TCVN5712, VSCII

Example

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

  1. test=> create database test02 with encoding 'UTF-8';
  2. 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.

    1. test=> \l template1
    2. List of databases
    3. Name | Owner | Encoding | Collate | Ctype | Access privileges
    4. -----------+----------+----------+-------------+-------------+-----------------------
    5. template1 | xxxxxxxx | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/xxxxxxxx +
    6. | | | | | xxxxxxxx=CTc/xxxxxxxx
    7. (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.

      1. test=> create database test03 with encoding 'EUC_CN';
      2. ERROR: encoding "EUC_CN" does not match locale "zh_CN.UTF-8"
      3. 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.

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

    1. 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.

  1. 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.

  1. encoding | collname | collcollate | collctype
  2. ------------+-----------------------+-----------------------+-----------------------
  3. | default | |
  4. | C | C | C
  5. | POSIX | POSIX | POSIX
  6. UTF8 | aa_DJ | aa_DJ.utf8 | aa_DJ.utf8
  7. LATIN1 | aa_DJ | aa_DJ | aa_DJ
  8. LATIN1 | aa_DJ.iso88591 | aa_DJ.iso88591 | aa_DJ.iso88591
  9. UTF8 | aa_DJ.utf8 | aa_DJ.utf8 | aa_DJ.utf8
  10. UTF8 | aa_ER | aa_ER | aa_ER
  11. UTF8 | aa_ER.utf8 | aa_ER.utf8 | aa_ER.utf8
  12. .......
  13. EUC_CN | zh_CN | zh_CN | zh_CN
  14. UTF8 | zh_CN | zh_CN.utf8 | zh_CN.utf8
  15. EUC_CN | zh_CN.gb2312 | zh_CN.gb2312 | zh_CN.gb2312
  16. UTF8 | zh_CN.utf8 | zh_CN.utf8 | zh_CN.utf8
  17. UTF8 | zh_HK | zh_HK.utf8 | zh_HK.utf8
  18. UTF8 | zh_HK.utf8 | zh_HK.utf8 | zh_HK.utf8
  19. EUC_CN | zh_SG | zh_SG | zh_SG
  20. UTF8 | zh_SG | zh_SG.utf8 | zh_SG.utf8
  21. EUC_CN | zh_SG.gb2312 | zh_SG.gb2312 | zh_SG.gb2312
  22. UTF8 | zh_SG.utf8 | zh_SG.utf8 | zh_SG.utf8
  23. EUC_TW | zh_TW | zh_TW.euctw | zh_TW.euctw
  24. UTF8 | zh_TW | zh_TW.utf8 | zh_TW.utf8
  25. EUC_TW | zh_TW.euctw | zh_TW.euctw | zh_TW.euctw
  26. UTF8 | zh_TW.utf8 | zh_TW.utf8 | zh_TW.utf8
  27. UTF8 | zu_ZA | zu_ZA.utf8 | zu_ZA.utf8
  28. LATIN1 | zu_ZA | zu_ZA | zu_ZA
  29. LATIN1 | zu_ZA.iso88591 | zu_ZA.iso88591 | zu_ZA.iso88591
  30. UTF8 | zu_ZA.utf8 | zu_ZA.utf8 | zu_ZA.utf8
  31. (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.

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

Note:

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

  1. test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';
  2. ERROR: new collation (zh_CN.utf8) is incompatible with the collation of the template database (zh_CN.UTF-8)
  3. 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.

    1. test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.UTF-8' lc_ctype='zh_CN.UTF-8';
    2. CREATE DATABASE
  • Use template0 as the template library. The command is as follows.

    1. test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';
    2. 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

Thank you! We've received your feedback.