PostgreSQL排序顺序异常排查：AWS RDS环境下的排序问题

问题 #

执行以下PostgreSQL查询：

with labels (label) as (
    values
        ('Asphalt Layer 2 - Labor (Hr)'::text),
        ('Asphalt Layer 2 - Labor Rate ($/Hr)'::text),
        ('Asphalt Layer 2 - Labor Cost'::text)
)
select *
from labels
order by 1;

实际得到的排序结果：

Asphalt Layer 2 - Labor Cost
Asphalt Layer 2 - Labor (Hr)
Asphalt Layer 2 - Labor Rate ($/Hr)

预期的正确排序（经JavaScript验证）：

Asphalt Layer 2 - Labor (Hr)
Asphalt Layer 2 - Labor Cost
Asphalt Layer 2 - Labor Rate ($/Hr)

使用的数据库环境：AWS RDS PostgreSQL 11.16（PostgreSQL 11.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit）

该排序差异导致crosstab查询中的值错位，请问问题出在哪里？

原因与解决方法 #

这不是操作错误，而是PostgreSQL的**排序规则（collation）**和JavaScript默认排序逻辑不一致导致的。

核心原因 #

PostgreSQL默认使用数据库的系统排序规则，这类规则通常会降低括号等特殊字符的排序权重，甚至忽略它们；而JavaScript的字符串排序是基于Unicode码点的，(的码点（U+0028）小于C的码点（U+0043），所以(Hr)会排在Cost前面。

解决办法 #

1. 指定Unicode码点排序规则
   在排序时指定"C"排序规则，让PostgreSQL完全按字符的Unicode码点排序，和JavaScript逻辑对齐：

   with labels (label) as (
       values
           ('Asphalt Layer 2 - Labor (Hr)'::text),
           ('Asphalt Layer 2 - Labor Rate ($/Hr)'::text),
           ('Asphalt Layer 2 - Labor Cost'::text)
   )
   select *
   from labels
   order by label collate "C";
   

2. 手动定义排序优先级
   如果无法修改排序规则，可以给每个标签绑定自定义排序值，强制指定顺序：

   with labels (label, sort_order) as (
       values
           ('Asphalt Layer 2 - Labor (Hr)'::text, 1),
           ('Asphalt Layer 2 - Labor Cost'::text, 2),
           ('Asphalt Layer 2 - Labor Rate ($/Hr)'::text, 3)
   )
   select label
   from labels
   order by sort_order;
   

3. 固定crosstab的列顺序
   针对crosstab场景，直接在查询中明确指定列的顺序，确保输出值和列一一对应：

   select * from crosstab(
       'select ... from ...', -- 你的原始数据查询
       $$values ('Asphalt Layer 2 - Labor (Hr)'::text), ('Asphalt Layer 2 - Labor Cost'::text), ('Asphalt Layer 2 - Labor Rate ($/Hr)'::text)$$
   ) as ct(
       "id" int,
       "Asphalt Layer 2 - Labor (Hr)" numeric,
       "Asphalt Layer 2 - Labor Cost" numeric,
       "Asphalt Layer 2 - Labor Rate ($/Hr)" numeric
   );
   

内容的提问来源于stack exchange，提问作者Matloob Siddiqi