PostgreSQL字符串排序行为异常原因咨询

你的排序结果和预期不符，核心原因是PostgreSQL的字符串排序依赖数据库的默认排序规则（collation），而非你预期的“按空格分割后分组排序”。

具体排序逻辑分析 #

你的三个目标字符串：

• Data Archiving/Back-Up/Recovery
• Database Management Software
• Data Warehousing/Mining

PostgreSQL会对字符串进行逐字符的规则化比较：

1. 前四个字符D-a-t-a完全一致，继续比较后续字符；
2. Data Archiving...的第五个字符是空格，Database...的第五个字符是小写b。在默认排序规则（如en_US.UTF-8）中，空格的排序权重低于字母，因此Data Archiving...排在最前面；
3. 比较Database...和Data Warehousing...时，Database...的第五个字符是b（排序规则不区分大小写，等价于大写B），Data Warehousing...的第五个字符是空格，第六个字符是W（等价于小写w）。由于B在排序规则中的顺序早于W，因此Database...被判定为更小的字符串，排在第二位。

你的预期是将所有Data 开头的字符串归为一组后排序，但PostgreSQL不会自动识别空格分割的子串，只会按整个字符串的collation规则逐字符对比。

验证与调整方法 #

1. 查看当前数据库的默认排序规则：

   SHOW collation;
   

2. 若要实现你预期的排序（按空格分割后的子串分组排序），可以显式指定排序依据：

   SELECT module
   FROM (
       VALUES ('Data Archiving/Back-Up/Recovery'), ('Database Management Software'), ('Data Warehousing/Mining')
   ) s(module)
   ORDER BY 
       split_part(module, ' ', 1),  -- 先按第一个空格前的内容排序
       split_part(module, ' ', 2) ASC;  -- 再按第一个空格后的内容排序
   

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