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.
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.
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
Create a database and specify LC_COLLATE and LC_CTYPE.
Use pg_dump or another client tool to logically export the data of the source database.
Use pg_restore or another client tool to import the exported data to the new database.