sql - Postgresql ORDER_BY ... COLLATE 不同的值返回相同的输出

Question

我在本地机器上使用提供的 COLLATE 值进行排序时遇到问题(在服务器上一切正常)。似乎没有任何效果。

使用 en_US COLLATE 的示例 SQL 代码:

SELECT username
FROM "user" 
WHERE (
  "user"."username" IN (
    'aaa', 
    'aab', 
    'aac', 
    'a.aa', 
    'a.ab', 
    'a.ac') 
ORDER BY "user"."username" COLLATE "en_US" ASC;

输出:

a.aa
a.ab
a.ac
aaa
aab
aac

使用 C COLLATE 的相同 SQL 查询返回相同的输出:

a.aa
a.ab
a.ac
aaa
aab
aac

但在服务器上它会针对不同的 COLLATE 返回不同的答案:

en_US:

aaa
aab
aac
a.aa
a.ab
a.ac

对于 C:

a.aa
a.ab
a.ac
aaa
aab
aac

本地机器:OSX、Postgres 9.6

SHOW LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

服务器机器:Ubuntu、Postgres 9.6

SHOW LC_COLLATE;
 lc_collate
------------
 en_US.utf8
(1 row)

SHOW LC_COLLATE; 查询输出之间存在差异，它们似乎都有效(或无效？)。

这里会出现什么问题？

Answer 1

PostgreSQL 默认使用操作系统 C 库的排序规则，所以解释是这些操作系统上的排序规则不同。

为避免该问题，请使用内置 ICU 支持的 PostgreSQL v10 或更高版本。然后(只要您使用相同版本的 ICU 库)这些排序规则在不同的操作系统中都是相同的。