有一个问题曾经困扰了客户很久：将数据从Sybase迁移到PG后，发现SQL输出的数据排序方式发生了变化。这种变化导致终端应用的数据显示与用户熟悉的数据排列不同，给最终用户带来了不便。
例如，现有表
CREATE TABLE t1 (
name varchar(10) NULL
);
插入以下数据：
INSERT INTO t1 values('aaa');
INSERT INTO t1 values(null);
INSERT INTO t1 values('abc') ;
INSERT INTO t1 values('ABC') ;
INSERT INTO t1 values('TTT') ;
INSERT INTO t1 values('bbc') ;
然后执行以下排序查询，比较Sybase和PG的排序结果，会发现两边的结果是不一样的。
SELECT * FROM t1 ORDER BY name
| Sybase | PG |
/1329777338.png) |
/3535826934.png)
|
Sybase默认排序特点： 2. 优先排大写字母，后再排小写字母。 | PG默认排序特点：
|
这时思维敏捷的你会问：是不是PG配置了不区分大小写引起的？能想到这个，首先给你点个赞，但不是这个原因。运行这个SQL：
SELECT 'ABC'='abc'
/328040132.png)
返回结果为False，说明PG的字符串比较实际上是区分大小写的。
为客户排忧解难是我们APJ的优良传统。当我们项目组获悉这个问题后，进行了深入分析，发现问题的根源在于PG采用了unicode字符集，而Sybase采用了CP850引起的。那CP850是什么呢？是一个古老的西欧字符集，详细资料参考这个page。不同的字符集，排序结果自然不一致。那如何解决呢？自然是要给PG指定一个与CP850一致排序方式。
在数据库中，COLLATE子句用于指定列的排序规则，它决定了字符数据的比较和排序行为。不同的COLLATE设置可以导致相同的字符数据在排序时表现出不同的顺序。COLLATE能够解决排序问题，因为它允许我们为特定的列或表达式指定一个明确的排序规则，从而确保数据的排序方式与特定字符集的排序规则相匹配。
因此针对这个排序问题，解决方案是为PG指定一个与CP850一致的排序方式来模拟Sybase的排序行为。经过分析，我们发现CP850是ASCII的一个扩展，而在PG中支持ASCII排序的正是POSIX。
于是，在PG里使用COLLATE指定排序方式，再次执行查询：
/1960472168.png)
可以看到，除去[NULL]，其他排序已经与Sybase一致了。
最后再加上SQL标准的 NULLS FIRST 子句，就能完美实现Sybase的默认排序了。
/2641032251.png)
Perfect!
其实我们也可以直接创建表或数据库时指定默认的COLLATE。这样，我们就不需要在每个ORDER BY语句中都加入COLLATE子句，从而避免了重复和潜在的错误。
在PostgreSQL中，通过以下语句在创建表时为特定的列指定COLLATE：
CREATE TABLE t1 (
name varchar(10) COLLATE "POSIX" NULL
);
或者，在创建数据库时设置默认的COLLATE。注意，需要使用LC_COLLATE关键字：
CREATE DATABASE mydb LC_COLLATE 'en_US.utf8';
在CP8项目中，我们通过指定COLLATE为”POSIX“，完美地复现了Sybase默认排序，解决了客户的难题，客户非常满意。