初始化数据库集群时，可以设置数据库的字符串排序、字符归类方法、数值格式、日期格式、时间格式、货币格式等。另外，为了支持国际化，数据库通常会涉及到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 * fromorder by collate "C";
使用本土化，改变操作符的结果。示例如下：
命令：
select * fromwhere > 'Tom' collate "C";
使用本土化索引进行排序
排序语句中的collate与索引的collate保持一致，才能使用这个索引进行排序。命令如下：
create index idxa on( collate "en_US"); explain select * from
order by collate "en_US";
输出结果示例如下：
QUERY PLAN ------------------------------------------------------------------------ Index Only Scan using idxa on(cost=0.15..31.55 rows=1360 width=64) (1 row)
设置输出结果按拼音排序
您可以通过如下四种方法来设置按拼音排序：
-
使用本土化 SQL。该方法不修改原有数据。命令如下：
select * fromorder by collate "en_US";
使用本土化字段。若已有数据，使用该方法时需要调整原有数据。命令如下：
alter tablealter type text COLLATE "en_US";
使用本土化索引以及本土化 SQL。该方法不修改原有数据。命令如下：
create index idxa on( collate "en_US"); explain select * from
order by collate "en_US";
输出结果示例如下：
QUERY PLAN ------------------------------------------------------------------------ Index Only Scan using idxa on(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
内容没看懂？ 不太想学习？想快速解决？ 有偿解决： 联系专家
阿里云企业补贴进行中： 马上申请
腾讯云限时活动1折起，即将结束： 马上收藏
同尘科技为腾讯云授权服务中心。
购买腾讯云产品享受折上折，更有现金返利:同意关联，立享优惠转转请注明出处：https://www.yunxiaoer.com/167070.html
赞 (0)阿里云RDS数据库RDS PostgreSQL权限管理最佳实践-云淘科技上一篇 2023年12月10日 上午9:54阿里云RDS数据库查找最耗费资源的 SQL（Top SQL）-云淘科技下一篇 2023年12月10日详情页2本站为广大会员提供阿里云、腾讯云、华为云、百度云等一线大厂的购买，续费优惠，保证底价，买贵退差。
-