PostgreSQL使用collate排序时特殊字符._未优先排序问题咨询

问题根因 #

你当前使用的C排序规则是严格按照ASCII码值排序的：.的ASCII码为46，大写字母A-Z的ASCII码范围为65~90，_的ASCII码为95，因此_会排在所有大写字母之后，不符合你的需求。

解决方案 #

方案1：单次查询自定义排序（无需额外权限，推荐） #

通过TRANSLATE函数将所有.和_临时替换为ASCII码远小于字母的控制字符，再进行排序，即可实现两类特殊字符优先于字母排序的效果，对字符串任意位置出现的.和_都生效：

SELECT * FROM test_spl_char 
ORDER BY TRANSLATE(CHR_NM, '._', CHR(1)||CHR(2)) COLLATE "C";

如果仅需要**首字符为./_**的条目优先，其余字符默认排序，可以用更轻量化的CASE判断写法：

SELECT * FROM test_spl_char 
ORDER BY
  -- 首字符为._的条目优先排序
  CASE WHEN CHR_NM ~ '^[._]' THEN 0 ELSE 1 END,
  -- 可调整：.排在_前面，不需要可删除该行
  CASE WHEN LEFT(CHR_NM,1) = '.' THEN 0 ELSE 1 END,
  -- 剩余按原有规则排序
  CHR_NM COLLATE "C";

方案2：创建自定义排序规则（适合多场景复用） #

如果该排序规则需要在多个查询中使用，可以基于ICU创建自定义排序规则（需要PostgreSQL 10及以上版本，且需要数据库超级用户权限）：

-- 创建自定义排序规则，指定.和_排在所有字母之前
CREATE COLLATION dot_underscore_first (
  provider = icu,
  rules = '&[first] < . < _ < A < a'
);

后续查询直接调用该规则即可：

SELECT * FROM test_spl_char ORDER BY CHR_NM COLLATE "dot_underscore_first";

预期输出 #

.GEMS
_SHAKE
BISCUIT
CANDY
DONUT
YUM

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