初始化数据库集群时，可以设置如下参数，用于设置数据库的字符串排序、字符归类方法、数值\日期\时间\货币的格式等。另外，为了支持国际化，数据库通常会涉及到 LC_COLLATE 和 LC_CTYPE 的概念。

您可以利用这些特性，按本土化需求，输出对应的顺序或者格式。本文将通过示例介绍如何设置数据库的本土化信息以及如何设置输出结果按中文的拼音顺序进行排序。

PostgreSQL 支持的字符集类型

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

查询字符集支持的 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                 | 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)

设置数据库的本土化（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          | UTF8  
 template0          | UTF8  
 db                 | SQL_ASCII  
 db1                | EUC_CN  
 contrib_regression | UTF8  
 test01             | UTF8  
 test02             | UTF8  
 postgres           | 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 INDEX  
postgres=# explain select * from a order by c1 collate "zh_CN";  
                               QUERY PLAN                                 
------------------------------------------------------------------------  
 Index Only Scan using idxa on a  (cost=0.15..31.55 rows=1360 width=64)  
(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 INDEX  
  postgres=# explain select * from a order by c1 collate "zh_CN";  
                                 QUERY PLAN                                 
  ------------------------------------------------------------------------  
   Index Only Scan using idxa on a  (cost=0.15..31.55 rows=1360 width=64)  
  (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 DATABASE  
  test02=# \c test03  
  You 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