PostgreSQL , create database , collate , ctype , pg_encoding , pg_encoding_to_char()
PostgreSQL实例支持创建多个数据库，创建数据库时，可以指定模板库，并为每个数据库设置不同的字符集、本地化collate, 货币等属性。
接下来举例讲述CREATE DATABASE的具体使用方法。
以test数据库为模板，克隆一个名为test01的数据库。
test=> create database test01 with template test; CREATE DATABASE如果不指定模板，则默认的模板为template1。
从指定模板库创建数据库，除了当前连接，不能有其他用户连在对应的模板库上面。
如果有其他用户连在test库，可能会报类似这样的错误
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.用户可以参考PostgreSQL的官方文档，有对应的字符集支持列表
https://www.postgresql.org/docs/9.6/static/multibyte.html
Server=Yes表示该字符集支持用于create database。否则只支持作为客户端字符集。
NameDescriptionLanguageServer?Bytes/CharAliasesBIG5Big FiveTraditional ChineseNo1-2WIN950, Windows950EUC_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, Windows936ISO_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)Yes1KOI8KOI8UKOI8-UCyrillic (Ukrainian)Yes1-LATIN1ISO 8859-1, ECMA 94Western EuropeanYes1ISO88591LATIN2ISO 8859-2, ECMA 94Central EuropeanYes1ISO88592LATIN3ISO 8859-3, ECMA 94South EuropeanYes1ISO88593LATIN4ISO 8859-4, ECMA 94North EuropeanYes1ISO88594LATIN5ISO 8859-9, ECMA 128TurkishYes1ISO88599LATIN6ISO 8859-10, ECMA 144NordicYes1ISO885910LATIN7ISO 8859-13BalticYes1ISO885913LATIN8ISO 8859-14CelticYes1ISO885914LATIN9ISO 8859-15LATIN1 with Euro and accentsYes1ISO885915LATIN10ISO 8859-16, ASRO SR 14111RomanianYes1ISO885916MULE_INTERNALMule internal codeMultilingual EmacsYes1-4-SJISShift JISJapaneseNo1-2Mskanji, ShiftJIS, WIN932, Windows932SHIFT_JIS_2004Shift JIS, JIS X 0213JapaneseNo1-2-SQL_ASCIIunspecified (see text)anyYes1-UHCUnified Hangul CodeKoreanNo1-2WIN949, Windows949UTF8Unicode, 8-bitallYes1-4UnicodeWIN866Windows CP866CyrillicYes1ALTWIN874Windows CP874ThaiYes1-WIN1250Windows CP1250Central EuropeanYes1-WIN1251Windows CP1251CyrillicYes1WINWIN1252Windows CP1252Western EuropeanYes1-WIN1253Windows CP1253GreekYes1-WIN1254Windows CP1254TurkishYes1-WIN1255Windows CP1255HebrewYes1-WIN1256Windows CP1256ArabicYes1-WIN1257Windows CP1257BalticYes1-WIN1258Windows CP1258VietnameseYes1ABC, TCVN, TCVN5712, VSCII创建一个UTF-8字符集的数据库
test=> create database test02 with encoding 'UTF-8'; CREATE DATABASE1. 指定的字符集必须是模板库字符集的超集，否则会报错。
2. 指定的lc_ctype和lc_collate必须与目标字符集兼容。
例子，template1是默认模板库，它的字符集为UTF8。
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)创建一个EUC_CN字符集的数据库
报错1，EUC_CN字符集与模板库的lc_collate,lc_ctype不兼容。
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".报错2，EUC_CN字符集与模板库的字符集UTF-8不兼容。
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.使用如下SQL可以查询系统表pg_collation得到字符集支持的lc_collate和lc_ctype。
其中encoding为空时，表示这个collation支持所有的字符集。
test=> select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ; 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)创建一个数据库，lc_collate, lc_ctype分别为zh_CN.utf8
test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8'; CREATE DATABASE如果指定的lc_collate, lc_ctype与模板库的collate,ctype不兼容，会报错。
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.解决办法1，使用兼容的collate和ctype。
test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.UTF-8' lc_ctype='zh_CN.UTF-8'; CREATE DATABASE解决办法2，使用template0作为模板库。
test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8'; CREATE DATABASE目前无法直接通过alter database的语法进行修改，用户可以使用创建新的数据库，导出，再导入的方式。
1. 创建新数据库，指定目标collate和ctype
2. 使用pg_dump或其他客户端工具逻辑导出源数据库的数据
3. 使用pg_restore或其他客户端工具，将第二步导出数据导入新数据库
https://www.postgresql.org/docs/9.6/static/sql-createdatabase.html
