初始化数据库集群时可以设置如下参数用于设置数据库的字符串排序、字符归类方法、数值\日期\时间\货币的格式等。另外为了支持国际化数据库通常会涉及到 LC_COLLATE 和 LC_CTYPE 的概念。LC_COLLATEString sort orderLC_CTYPECharacter classification (What is a letter? Its upper-case equivalent?)LC_MESSAGESLanguage of messagesLC_MONETARYFormatting of currency amountsLC_NUMERICFormatting of numbersLC_TIMEFormatting of dates and times您可以利用这些特性按本土化需求输出对应的顺序或者格式。本文将通过示例介绍如何设置数据库的本土化信息以及如何设置输出结果按中文的拼音顺序进行排序。PostgreSQL 支持的字符集类型您可以通过 PostgreSQL 的官方文档或下表查看对应的字符集支持列表ServerYes 表示该字符集支持用于CREATE DATABASE命令ServerNo 表示只支持作为客户端字符集。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查询字符集支持的 LC_COLLATE 和 LC_CTYPE 信息您可以使用如下 SQL 查询系统表 pg_collation来获取字符集支持的 LC_COLLATE 和 LC_CTYPE 信息。test select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;返回结果如下所示encoding 为空时表示这个 collation 支持所有的字符集。encoding | collname | collcollate | collctype---------------------------------------------------------------------------------| default | || C | C | C| POSIX | POSIX | POSIXUTF8 | aa_DJ | aa_DJ.utf8 | aa_DJ.utf8LATIN1 | aa_DJ | aa_DJ | aa_DJLATIN1 | aa_DJ.iso88591 | aa_DJ.iso88591 | aa_DJ.iso88591UTF8 | aa_DJ.utf8 | aa_DJ.utf8 | aa_DJ.utf8UTF8 | aa_ER | aa_ER | aa_ERUTF8 | aa_ER.utf8 | aa_ER.utf8 | aa_ER.utf8.......EUC_CN | zh_CN | zh_CN | zh_CNUTF8 | zh_CN | zh_CN.utf8 | zh_CN.utf8EUC_CN | zh_CN.gb2312 | zh_CN.gb2312 | zh_CN.gb2312UTF8 | zh_CN.utf8 | zh_CN.utf8 | zh_CN.utf8UTF8 | zh_HK | zh_HK.utf8 | zh_HK.utf8UTF8 | zh_HK.utf8 | zh_HK.utf8 | zh_HK.utf8EUC_CN | zh_SG | zh_SG | zh_SGUTF8 | zh_SG | zh_SG.utf8 | zh_SG.utf8EUC_CN | zh_SG.gb2312 | zh_SG.gb2312 | zh_SG.gb2312UTF8 | zh_SG.utf8 | zh_SG.utf8 | zh_SG.utf8EUC_TW | zh_TW | zh_TW.euctw | zh_TW.euctwUTF8 | zh_TW | zh_TW.utf8 | zh_TW.utf8EUC_TW | zh_TW.euctw | zh_TW.euctw | zh_TW.euctwUTF8 | zh_TW.utf8 | zh_TW.utf8 | zh_TW.utf8UTF8 | zu_ZA | zu_ZA.utf8 | zu_ZA.utf8LATIN1 | zu_ZA | zu_ZA | zu_ZALATIN1 | zu_ZA.iso88591 | zu_ZA.iso88591 | zu_ZA.iso88591UTF8 | zu_ZA.utf8 | zu_ZA.utf8 | zu_ZA.utf8(869 rows)设置数据库的本土化collate信息关于如何设置字符集、LC_COLLATE 及 LC_CTYPE 的信息请参见文档 CREATE DATABASE 命令的具体使用方法。设置字段的本土化前提条件执行如下 SQL 命令查询当前数据库的字符集encoding类型并了解清楚与您当前数据库字符集兼容的 collate。postgres# select datname,pg_encoding_to_char(encoding) as encoding from pg_database;返回结果如下所示datname | encoding-------------------------------template1 | UTF8template0 | UTF8db | SQL_ASCIIdb1 | EUC_CNcontrib_regression | UTF8test01 | UTF8test02 | UTF8postgres | UTF8(8 rows)操作步骤在创建表时执行如下命令指定兼容当前字符集的 collate。CREATE TABLE test1 (a text COLLATE de_DE,b text COLLATE es_ES,...);执行如下命令修改列 collate。注意修改列 collate 时会导致 rewrite table大表请谨慎操作。alter table a alter c1 type text COLLATE zh_CN;在 SQL 使用本土化使用本土化改变 order by 输出排序。test# select * from a order by c1 collate C;c1--------刘少奇刘德华(2 rows)test# select * from a order by c1 collate zh_CN;c1--------刘德华刘少奇(2 rows)使用本土化改变操作符的结果。test# select * from a where c1 刘少奇 collate C;c1--------刘德华(1 row)test# select * from a where c1 刘少奇 collate zh_CN;c1----(0 rows)使用本土化索引进行排序排序语句中的 collate 与索引的 collate 保持一致才能使用这个索引进行排序。postgres# create index idxa on a(c1 collate zh_CN);CREATE INDEXpostgres# explain select * from a order by c1 collate zh_CN;QUERY PLAN------------------------------------------------------------------------Index Only Scan using idxa on a (cost0.15..31.55 rows1360 width64)(1 row)设置输出结果按拼音排序您可以通过如下四种方法来设置按拼音排序使用本土化 SQL。该方法不修改原有数据。test# select * from a order by c1 collate zh_CN;c1--------刘德华刘少奇(2 rows)使用本土化字段。若已有数据使用该方法时需要调整原有数据。alter table a alter c1 type text COLLATE zh_CN;使用本土化索引以及本土化 SQL。该方法不修改原有数据。postgres# create index idxa on a(c1 collate zh_CN);CREATE INDEXpostgres# explain select * from a order by c1 collate zh_CN;QUERY PLAN------------------------------------------------------------------------Index Only Scan using idxa on a (cost0.15..31.55 rows1360 width64)(1 row)将数据库的 collate 设置为 zh_CN数据会将默认使用这个 collate 按拼音排序。test02# create database test03 encoding UTF8 lc_collate zh_CN.utf8 lc_ctype zh_CN.utf8 template template0;CREATE DATABASEtest02# \c test03You are now connected to database test03 as user postgres.test03# select * from (values (刘德华),(刘少奇)) as a(c1) order by c1 ;c1--------刘德华刘少奇(2 rows)注意在设置按拼音排序时要注意多音字。例如重庆chongqing在编码时”重”可能会按照 zhong 编码影响输出如下面的例子所示test03# select * from (values (中山),(重庆)) as a(c1) order by c1 collate zh_CN;c1------中山重庆(2 rows)在 Greenplum 中设置输出结果按拼音排序Greenplum 不支持单列设置 collate按拼音排序有些许不同。在 Greenplum 中可以使用字符集转换按对应二进制排序得到拼音排序的效果如下面的例子所示postgres# select * from (values (刘德华), (刘少奇)) t(id) order by byteain(textout(convert(id,UTF8,EUC_CN)));id--------刘德华刘少奇(2 rows)参考文档CREATE DATABASE 命令的具体使用方法PostgreSQL 9.6.2 Documentation — Chapter 23. Localization 设置PostgreSQL字符串排序规则（COLLATE、CTYPE）
初始化数据库集群时可以设置如下参数用于设置数据库的字符串排序、字符归类方法、数值\日期\时间\货币的格式等。另外为了支持国际化数据库通常会涉及到 LC_COLLATE 和 LC_CTYPE 的概念。LC_COLLATEString sort orderLC_CTYPECharacter classification (What is a letter? Its upper-case equivalent?)LC_MESSAGESLanguage of messagesLC_MONETARYFormatting of currency amountsLC_NUMERICFormatting of numbersLC_TIMEFormatting of dates and times您可以利用这些特性按本土化需求输出对应的顺序或者格式。本文将通过示例介绍如何设置数据库的本土化信息以及如何设置输出结果按中文的拼音顺序进行排序。PostgreSQL 支持的字符集类型您可以通过 PostgreSQL 的官方文档或下表查看对应的字符集支持列表ServerYes 表示该字符集支持用于CREATE DATABASE命令ServerNo 表示只支持作为客户端字符集。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查询字符集支持的 LC_COLLATE 和 LC_CTYPE 信息您可以使用如下 SQL 查询系统表 pg_collation来获取字符集支持的 LC_COLLATE 和 LC_CTYPE 信息。test select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;返回结果如下所示encoding 为空时表示这个 collation 支持所有的字符集。encoding | collname | collcollate | collctype---------------------------------------------------------------------------------| default | || C | C | C| POSIX | POSIX | POSIXUTF8 | aa_DJ | aa_DJ.utf8 | aa_DJ.utf8LATIN1 | aa_DJ | aa_DJ | aa_DJLATIN1 | aa_DJ.iso88591 | aa_DJ.iso88591 | aa_DJ.iso88591UTF8 | aa_DJ.utf8 | aa_DJ.utf8 | aa_DJ.utf8UTF8 | aa_ER | aa_ER | aa_ERUTF8 | aa_ER.utf8 | aa_ER.utf8 | aa_ER.utf8.......EUC_CN | zh_CN | zh_CN | zh_CNUTF8 | zh_CN | zh_CN.utf8 | zh_CN.utf8EUC_CN | zh_CN.gb2312 | zh_CN.gb2312 | zh_CN.gb2312UTF8 | zh_CN.utf8 | zh_CN.utf8 | zh_CN.utf8UTF8 | zh_HK | zh_HK.utf8 | zh_HK.utf8UTF8 | zh_HK.utf8 | zh_HK.utf8 | zh_HK.utf8EUC_CN | zh_SG | zh_SG | zh_SGUTF8 | zh_SG | zh_SG.utf8 | zh_SG.utf8EUC_CN | zh_SG.gb2312 | zh_SG.gb2312 | zh_SG.gb2312UTF8 | zh_SG.utf8 | zh_SG.utf8 | zh_SG.utf8EUC_TW | zh_TW | zh_TW.euctw | zh_TW.euctwUTF8 | zh_TW | zh_TW.utf8 | zh_TW.utf8EUC_TW | zh_TW.euctw | zh_TW.euctw | zh_TW.euctwUTF8 | zh_TW.utf8 | zh_TW.utf8 | zh_TW.utf8UTF8 | zu_ZA | zu_ZA.utf8 | zu_ZA.utf8LATIN1 | zu_ZA | zu_ZA | zu_ZALATIN1 | zu_ZA.iso88591 | zu_ZA.iso88591 | zu_ZA.iso88591UTF8 | zu_ZA.utf8 | zu_ZA.utf8 | zu_ZA.utf8(869 rows)设置数据库的本土化collate信息关于如何设置字符集、LC_COLLATE 及 LC_CTYPE 的信息请参见文档 CREATE DATABASE 命令的具体使用方法。设置字段的本土化前提条件执行如下 SQL 命令查询当前数据库的字符集encoding类型并了解清楚与您当前数据库字符集兼容的 collate。postgres# select datname,pg_encoding_to_char(encoding) as encoding from pg_database;返回结果如下所示datname | encoding-------------------------------template1 | UTF8template0 | UTF8db | SQL_ASCIIdb1 | EUC_CNcontrib_regression | UTF8test01 | UTF8test02 | UTF8postgres | UTF8(8 rows)操作步骤在创建表时执行如下命令指定兼容当前字符集的 collate。CREATE TABLE test1 (a text COLLATE de_DE,b text COLLATE es_ES,...);执行如下命令修改列 collate。注意修改列 collate 时会导致 rewrite table大表请谨慎操作。alter table a alter c1 type text COLLATE zh_CN;在 SQL 使用本土化使用本土化改变 order by 输出排序。test# select * from a order by c1 collate C;c1--------刘少奇刘德华(2 rows)test# select * from a order by c1 collate zh_CN;c1--------刘德华刘少奇(2 rows)使用本土化改变操作符的结果。test# select * from a where c1 刘少奇 collate C;c1--------刘德华(1 row)test# select * from a where c1 刘少奇 collate zh_CN;c1----(0 rows)使用本土化索引进行排序排序语句中的 collate 与索引的 collate 保持一致才能使用这个索引进行排序。postgres# create index idxa on a(c1 collate zh_CN);CREATE INDEXpostgres# explain select * from a order by c1 collate zh_CN;QUERY PLAN------------------------------------------------------------------------Index Only Scan using idxa on a (cost0.15..31.55 rows1360 width64)(1 row)设置输出结果按拼音排序您可以通过如下四种方法来设置按拼音排序使用本土化 SQL。该方法不修改原有数据。test# select * from a order by c1 collate zh_CN;c1--------刘德华刘少奇(2 rows)使用本土化字段。若已有数据使用该方法时需要调整原有数据。alter table a alter c1 type text COLLATE zh_CN;使用本土化索引以及本土化 SQL。该方法不修改原有数据。postgres# create index idxa on a(c1 collate zh_CN);CREATE INDEXpostgres# explain select * from a order by c1 collate zh_CN;QUERY PLAN------------------------------------------------------------------------Index Only Scan using idxa on a (cost0.15..31.55 rows1360 width64)(1 row)将数据库的 collate 设置为 zh_CN数据会将默认使用这个 collate 按拼音排序。test02# create database test03 encoding UTF8 lc_collate zh_CN.utf8 lc_ctype zh_CN.utf8 template template0;CREATE DATABASEtest02# \c test03You are now connected to database test03 as user postgres.test03# select * from (values (刘德华),(刘少奇)) as a(c1) order by c1 ;c1--------刘德华刘少奇(2 rows)注意在设置按拼音排序时要注意多音字。例如重庆chongqing在编码时”重”可能会按照 zhong 编码影响输出如下面的例子所示test03# select * from (values (中山),(重庆)) as a(c1) order by c1 collate zh_CN;c1------中山重庆(2 rows)在 Greenplum 中设置输出结果按拼音排序Greenplum 不支持单列设置 collate按拼音排序有些许不同。在 Greenplum 中可以使用字符集转换按对应二进制排序得到拼音排序的效果如下面的例子所示postgres# select * from (values (刘德华), (刘少奇)) t(id) order by byteain(textout(convert(id,UTF8,EUC_CN)));id--------刘德华刘少奇(2 rows)参考文档CREATE DATABASE 命令的具体使用方法PostgreSQL 9.6.2 Documentation — Chapter 23. Localization