设置PostgreSQL排序规则（Collation） - 云数据库 RDS

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

LC_COLLATE	String sort order
LC_CTYPE	字符分类
LC_MESSAGES	消息的语言
LC_MONETARY	货币使用的格式
LC_NUMERIC	数字使用的格式
LC_TIME	时间日期使用的格式

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

PostgreSQL 支持的字符集类型

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

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

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

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。

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，大表请谨慎操作。
- 如下示例中test1为表名，a为待修改列collate的列名。
```
alter table test1 alter a type text COLLATE "en_US";
```

在SQL使用本土化
- 使用本土化，改变order by输出排序。命令如下：
```
select * from <table_name> order by <column_name> collate "C";  
```
- 使用本土化，改变操作符的结果。示例如下：
  命令：
```
select * from <table_name> where <column_name> > 'Tom' collate "C";  
```

使用本土化索引进行排序

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

create index idxa on <table_name>(<column_name> collate "en_US");  
explain select * from <table_name> order by <column_name> collate "en_US";

输出结果示例如下：

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

设置输出结果按拼音排序

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

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

select * from <table_name> order by <column_name> collate "en_US";

使用本土化字段。若已有数据，使用该方法时需要调整原有数据。命令如下：
```
alter table <table_name> alter <column_name> type text COLLATE "en_US";
```

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

create index idxa on <table_name>(<column_name> collate "en_US");  
explain select * from <table_name> order by <column_name> collate "en_US";

输出结果示例如下：

                                 QUERY PLAN
  ------------------------------------------------------------------------
   Index Only Scan using idxa on <table_name>  (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)

参考文档

PostgreSQL 15 Documentation — Chapter 23. Localization