PostgreSQL 支持的字符集类型

您可以参考PostgreSQL 的官方文档。

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

• 设置字段的本土化

  前提条件

  执行如下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)

  操作步骤

  1. 在创建表时，执行如下命令，指定兼容当前字符集的collate。

     CREATE TABLE test1 (
      a text COLLATE "de_DE",
      b text COLLATE "es_ES",
      ...
     );

  2. 执行如下命令，修改列collate。
     说明 修改列collate时，会导致rewrite table，大表请谨慎操作。

     alter table a alter c1 type text COLLATE "en_US";

• 在SQL使用本土化
  • 使用本土化，改变order by输出排序。命令如下：

    test=# select * from a order by c1 collate "C";

    输出结果如下：

    c1
      --------
       Tom
       Alice
      (2 rows)
      test=# select * from a order by c1 collate "en_US";
         c1
      --------
       Alice
       Tom
      (2 rows)

  • 使用本土化，改变操作符的结果。示例如下：

    命令：

    select * from a where c1 > 'Tom' collate "C";

    输出结果如下：

    c1
      --------
       Alice
      (1 row)

    命令：

    select * from a where c1 > 'Tom' collate "en_US";

    输出结果如下：

    c1
      ----
      (0 rows)

• 使用本土化索引进行排序

  排序语句中的collate与索引的collate保持一致，才能使用这个索引进行排序。命令如下：

  create index idxa on a(c1 collate "en_US");  
  explain select * from a order by c1 collate "en_US";

  输出结果如下：

  QUERY PLAN
  ------------------------------------------------------------------------
   Index Only Scan using idxa on a  (cost=0.15..31.55 rows=1360 width=64)
  (1 row)

设置输出结果按拼音排序

您可以通过如下四种方法来设置按拼音排序：

• 使用本土化 SQL。该方法不修改原有数据。命令如下：

  select * from a order by c1 collate "en_US";

  输出结果如下：

  c1
    --------
     Alice
     Tom
    (2 rows)

• 使用本土化字段。若已有数据，使用该方法时需要调整原有数据。命令如下：

  alter table a alter c1 type text COLLATE "en_US";

• 使用本土化索引以及本土化 SQL。该方法不修改原有数据。命令如下：

  create index idxa on a(c1 collate "en_US");  
  explain select * from a order by c1 collate "en_US";

  输出结果如下：

  QUERY PLAN
    ------------------------------------------------------------------------
     Index Only Scan using idxa on a  (cost=0.15..31.55 rows=1360 width=64)
    (1 row)

• 将数据库的collate设置为en_US，数据会将默认使用这个collate按拼音排序。命令如下：

  create database test03 encoding 'UTF8' lc_collate 'en_US.utf8' lc_ctype 'en_US.utf8'  template template0;
  \c test03
  select * from (values ('Alice'),('Tom')) as a(c1) order by c1 ;

  输出结果如下：

  c1
    --------
     Alice
     Tom
    (2 rows)

  说明 如果是中文，在设置按拼音排序时，要注意多音字。例如重庆（chongqing），在编码时，重可能会按照zhong编码，影响输出。

在Greenplum中设置输出结果按拼音排序

Greenplum不支持单列设置collate，按拼音排序有些许不同。

在Greenplum中，可以使用字符集转换，按对应二进制排序，得到拼音排序的效果，如下面的命令所示：

select * from (values ('Alice'), ('Tom')) t(id) order by byteain(textout(convert(id,'UTF8','EUC_CN')));

输出结果如下：

id
--------
 Alice
 Tom
(2 rows)

参考文档