PostgreSQL 从开发要换PG表字段的 collaion 说起 到 程序员别异想天开了

2021年绝对是一个有意思的念头，估计过10年都会想到今年的一些变革，很多人都会被影响，改变，重新开始，或寻找新的路径。

归正题，新公司的开发小朋友，对DB 提出了一个问题，就是要修改某个表的字段的collation,究其原因为了某些业务中这个字段的排序。然后我就告诉DB，NO NO NO ，究其原因曾经SQL SERVER 更换collation后产生的问题还在记忆里面。不过PG的collation  说起来还真没有怎么研究，空白要被填补，否则不能一致倚老卖老。

首先要确认几点，

1   PG 要更换collation 针对表的字段，主要就是文本的类型 char ,varchar, text. 

2   collation 与PG的 encoding 有关，于创建数据库INSTANCE 初始化有关，与建库时的建库语句有关，也与建表的语句有关。

3     网上我是没有找到中文关于这类的问题清晰的描述或文章，我就当开个头。

从PG的encoding 说起，PG 的encoding 分为 server encoding 和 client encoding 

通过下面的语句，我们可以找到当前数据库的关于encoding, collation 的信息

SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;

数据库的encoding 是UTF8 ,客户的client encoding 也是UTF8

问题1  我的client_encoding 和 server_encoding 可以不一样吗？

我们来看看下面的操作，将客户端的encoding 变为 BIG5码， 则在键入一些字符，系统会直接报错，无法确认这些字符是什么。

而将客户端的encoding 更换为 GBK，我们在查询俄文，也不会报错

这就涉及到PG 本身支持的字符编码类型，这里台湾省的BIG5码不在支持范围（以PG13为例），中国的GBK属于PG的扩展标准。  下方是一个表，这个表里面server ICU 为NO 的是 PG 目前不支持的字符编码。

而UTF8 为什么为一个大多数PG的字符的标准，因为这个编码是 ALL，也就是通吃各种字符以及国别的语言。

而这些是怎么带入到我们的建库语句中，参见下图， create database encoding 以及 LC_COLLATE, LC_CTYPE 这三个选项。

en-coding 是字符的编码集，此数据库下的所有的默认表的设置，以及字段的编码都为UTF8 ， lc_collate 为本地化字符排序规则，lc_ctype 为字符集中的字符分类，这里统计了一下关于UTF8的类型的字符分类，在PG14中有262个。

我们创建一个使用collate 为 zh_CN的数据库，对比 en_US的数据库又什么不同

我们在两个数据库中的同样的表，中插入同样的数据，然后order by 一下

en utf8

zh utf8

那么此时差距就有了，同样都是一样的字符，在排序后，同样都使用UTF8 ，而在collate 不同的情况下， 默认的name  进行排序后的结果就不一致了。

那么怎么能让两个表的order by 的结果一致。

ALTER TABLE test ALTER COLUMN name SET DATA TYPE character varying(255) COLLATE "en_US";

通过修改表的字段的collation后，再次进行order by ，两个表的结果一致了。

说到这里，回到上面的问题，开发要改字段的collation，为什么没有让他改。

原因

1   不确认修改的字段是否会引起索引的重建的问题

2   修改后，部分查询会出现无法进行的情况

上面两个图是字段的collation 是一致的情况下，可以查询出数据（下面的图），如果我修改了某个表的collation ,则查询直接报错。

2021-09-02 01:18:51.116 EDT [48166] ERROR:  could not determine which collation to use for string hashing
2021-09-02 01:18:51.116 EDT [48166] HINT:  Use the COLLATE clause to set the collation explicitly.
2021-09-02 01:18:51.116 EDT [48166] STATEMENT:  select t.id  from test as t inner join test_2 as t2 on t.name = t2.name order by t.name;
ERROR:  could not determine which collation to use for string hashing
HINT:  Use the COLLATE clause to set the collation explicitly.
test_zh=#

主要的原因还是表join 的情况下，使用到了hash 算法，而人类看上去明明一样的两个表的 两个字段，在你修改了collation后，那就彻彻底底的不一样了。

到此为止，还应该深入，但想睡觉了，不过很想和那个程序员说， 您还是别异想天开了，我们应该有其他的方法解决你的需求。