PostgreSQL 实例支持创建多个数据库。创建数据库时，您可以指定模板库，并为每个数据库设置不同的字符集、本地化 collate、货币等属性。

本文将通过使用示例来介绍如何通过CREATE DATABASE命令来设置模板库、字符集、LC_COLLATE 及 LC_CTYPE 的信息。

CREATE DATABASE 命令的语法

    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 ] ]

克隆模板库

克隆模板库是指从指定模板创建数据库。如下示例将介绍如何通过CREATE DATABASE命令从指定模板创建数据库。

使用示例

以 test 数据库为模板，克隆一个名为 test01 的数据库，其命令如下所示：

    1. test=> create database test01 with template test;  
    2. CREATE DATABASE

注意：

  • 若不指定模板，则默认的模板为 template1。

  • 从指定模板库创建数据库时，除了当前连接，不能有其他用户连在对应的模板库上面。例如，若有其他用户连在 test 数据库上，可能会报类似如下的错误：

    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.

指定字符集类型

创建数据库时，您可以指定 encoding。如下示例将介绍如何通过CREATE DATABASE命令创建指定字符集类型的数据库。

PostgreSQL 支持的字符集类型

您可以通过 PostgreSQL 的官方文档或下表查看对应的字符集支持列表，Server=Yes 表示该字符集支持用于CREATE DATABASE命令，Server=No 表示只支持作为客户端字符集。

NameDescriptionLanguageServer?Bytes/CharAliases
BIG5Big FiveTraditional ChineseNo1-2WIN950, Windows950
EUC_CNExtended UNIX Code-CNSimplified ChineseYes1-3-
EUC_JPExtended UNIX Code-JPJapaneseYes1-3-
EUC_JIS_2004Extended UNIX Code-JP, JIS X 0213JapaneseYes1-3-
EUC_KRExtended UNIX Code-KRKoreanYes1-3-
EUC_TWExtended UNIX Code-TWTraditional Chinese, TaiwaneseYes1-3-
GB18030National StandardChineseNo1-4-
GBKExtended National StandardSimplified ChineseNo1-2WIN936, Windows936
ISO_8859_5ISO 8859-5, ECMA 113Latin/CyrillicYes1-
ISO_8859_6ISO 8859-6, ECMA 114Latin/ArabicYes1-
ISO_8859_7ISO 8859-7, ECMA 118Latin/GreekYes1-
ISO_8859_8ISO 8859-8, ECMA 121Latin/HebrewYes1-
JOHABJOHABKorean (Hangul)No1-3-
KOI8RKOI8-RCyrillic (Russian)Yes1KOI8
KOI8UKOI8-UCyrillic (Ukrainian)Yes1-
LATIN1ISO 8859-1, ECMA 94Western EuropeanYes1ISO88591
LATIN2ISO 8859-2, ECMA 94Central EuropeanYes1ISO88592
LATIN3ISO 8859-3, ECMA 94South EuropeanYes1ISO88593
LATIN4ISO 8859-4, ECMA 94North EuropeanYes1ISO88594
LATIN5ISO 8859-9, ECMA 128TurkishYes1ISO88599
LATIN6ISO 8859-10, ECMA 144NordicYes1ISO885910
LATIN7ISO 8859-13BalticYes1ISO885913
LATIN8ISO 8859-14CelticYes1ISO885914
LATIN9ISO 8859-15LATIN1 with Euro and accentsYes1ISO885915
LATIN10ISO 8859-16, ASRO SR 14111RomanianYes1ISO885916
MULE_INTERNALMule internal codeMultilingual EmacsYes1-4-
SJISShift JISJapaneseNo1-2Mskanji, ShiftJIS, WIN932, Windows932
SHIFT_JIS_2004Shift JIS, JIS X 0213JapaneseNo1-2-
SQL_ASCIIunspecified (see text)anyYes1-
UHCUnified Hangul CodeKoreanNo1-2WIN949, Windows949
UTF8Unicode, 8-bitallYes1-4Unicode
WIN866Windows CP866CyrillicYes1ALT
WIN874Windows CP874ThaiYes1-
WIN1250Windows CP1250Central EuropeanYes1-
WIN1251Windows CP1251CyrillicYes1WIN
WIN1252Windows CP1252Western EuropeanYes1-
WIN1253Windows CP1253GreekYes1-
WIN1254Windows CP1254TurkishYes1-
WIN1255Windows CP1255HebrewYes1-
WIN1256Windows CP1256ArabicYes1-
WIN1257Windows CP1257BalticYes1-
WIN1258Windows CP1258VietnameseYes1ABC, TCVN, TCVN5712, VSCII

使用示例

创建一个字符集为 UTF-8 的数据库，其命令如下所示：

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

注意：

  • 指定的字符集必须是模板库字符集的超集，否则会报错。

  • 指定的 LC_COLLATE 和 LC_CTYPE 必须与目标字符集兼容，否则会报错。

  • 报错示例：template1 是默认模板库，它的字符集为 UTF8，如下所示。

    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)

当使用上述 template1 作为模板创建一个字符集为 EUC_CN 的数据库时，会出现如下错误：

  • EUC_CN 字符集与模板库的 LC_COLLATE 和 LC_CTYPE 不兼容。
    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".
  • EUC_CN 字符集与模板库的字符集 UTF-8 不兼容。
    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.
  • 解决方法示例：使用 template0 作为模板库，即可解决上述报错的问题。
    1.   create database test03 with encoding 'EUC_CN' template template0;

设置 LC_COLLATE 和 LC_CTYPE 的信息

本示例将介绍如何查询字符集支持的 LC_COLLATE 和 LC_CTYPE、如何通过CREATE DATABASE命令指定 LC_COLLATE 和 LC_CTYPE 以及如何修改已有数据库的 LC_COLLATE 和 LC_CTYPE。

查询字符集支持的 LC_COLLATE 和 LC_CTYPE 信息

您可以使用如下 SQL 查询系统表 pg_collation，来获取字符集支持的LC_COLLATE 和 LC_CTYPE 信息。

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

返回结果如下所示，encoding 为空时，表示这个 collation 支持所有的字符集。

    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)

创建数据库时指定 LC_COLLATE 和 LC_CTYPE

使用示例

创建一个 LC_COLLATE 和 LC_CTYPE 分别为 zh_CN.utf8 的数据库，其命令如下所示：

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

注意:

若指定的 LC_COLLATE 和 LC_CTYPE 与模板库的 LC_COLLATE 和 LC_CTYPE 不兼容，会出现如下错误：

    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.

出现上述错误时，有如下两种解决方法：

  • 使用兼容的 LC_COLLATE 和 LC_CTYPE，其命令如下所示：
    1. test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.UTF-8' lc_ctype='zh_CN.UTF-8';  
    2. CREATE DATABASE
  • 使用 template0 作为模板库，其命令如下所示：
    1. test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
    2. CREATE DATABASE

修改已有数据库的 LC_COLLATE 和 LC_CTYPE 信息

目前，您无法直接通过 alter database 命令修改已有数据库的 LC_COLLATE 和 LC_CTYPE 信息，但可以通过创建新的数据库，然后导出再导入数据的方式进行修改。

操作步骤

  • 创建新数据库，指定目标 LC_COLLATE 和 LC_CTYPE。

  • 使用 pg_dump 或其它客户端工具逻辑导出源数据库的数据。

  • 使用 pg_restore 或其它客户端工具，将第 2 步导出的数据导入新数据库。

参考文档

PostgreSQL 9.6.2 Documentation — CREATE DATABASE