PostgreSQL文本字段排序不符合预期问题排查

问题原因 #

你遇到的排序结果是符合en_US.UTF8 locale默认行为的：

• 在ASCII编码中，大写字母（A-Z）的字符数值（65-90）小于小写字母（a-z，97-122）
• en_US.UTF8的默认排序规则是大小写敏感的，所以大写开头的字符串会优先排在小写开头的字符串前面，这就导致IKvap06:52:22.52和IQKPb06:55:51.53出现在i.lost.all.my.tacos之前。

解决办法 #

如果需要实现不区分大小写的排序，可以用以下几种方式：

• 临时转换排序：通过LOWER()函数统一转换为小写后排序，语句如下：

  SELECT column_A FROM table ORDER BY LOWER(column_A) ASC;
  

• 使用自定义排序规则：创建一个不区分大小写的collation，之后排序时指定该规则：

  -- 创建不区分大小写的排序规则
  CREATE COLLATION en_us_ci (provider = icu, locale = 'en-US-u-ks-level2');
  -- 使用该规则排序
  SELECT column_A FROM table ORDER BY column_A COLLATE en_us_ci ASC;
  

• 字段级指定排序规则：如果需要永久生效，可以在创建表时给字段指定该collation：

  CREATE TABLE your_table (
      column_A TEXT COLLATE en_us_ci
      -- 其他字段...
  );
  

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