PostgreSQL为何部分排序区分大小写、部分不区分？

问题场景 #

执行以下SQL操作：

create temp table test (first_name text, last_name text);
insert into test values
  ('Hanna', 'Beat'),
  ('JOAN', 'BEET'),
  ('Mark', 'Bernstein'),
  ('ALFRED', 'DOE'),
  ('henry', 'doe'),
  ('Henry', 'Doe'),
  ('Dennis', 'Doe');
select last_name, first_name from test order by last_name, first_name;

得到实际结果：

last_name | first_name 
-----------+------------
 Beat      | Hanna
 BEET      | JOAN
 Bernstein | Mark
 doe       | henry
 Doe       | Dennis
 Doe       | Henry
 DOE       | ALFRED
(7 rows)

困惑点：前三条排序看似不区分大小写，后四条却区分大小写，既不符合完全区分大小写的预期，也不符合完全不区分大小写的预期。

环境信息：

# show lc_collate; show lc_ctype;
 lc_collate  
-------------
 en_US.UTF-8
(1 row)

  lc_ctype   
-------------
 en_US.UTF-8
(1 row)

原因解析 #

核心是en_US.UTF-8排序规则（由lc_collate控制）的双层比较逻辑：

• 第一层：不区分大小写的字典序比较
  当两个字符串的「不区分大小写版本」字典序不同时，直接按该字典序排序，忽略大小写。比如：

  • Beat和BEET的不区分大小写版本是beat和beet，beat的第三个字符a字典序小于beet的e，因此Beat排在BEET前；
  • BEET和Bernstein的不区分大小写版本是beet和bernstein，beet的第三个字符e小于bernstein的r，因此BEET排在Bernstein前。
    这就是前三条看起来「不区分大小写」的原因。

• 第二层：区分大小写的精细排序
  当两个字符串的「不区分大小写版本」完全相同时，会进一步按字符的大小写权重排序：小写字符权重低于大写字符，因此全小写字符串排在最前，首大写次之，全大写最后。

  同时你的排序语句是order by last_name, first_name，当last_name优先级相同时（比如两条Doe），会按first_name的字典序排序：Dennis的字典序小于Henry，因此Doe | Dennis排在Doe | Henry前。

总结 #

实际结果是en_US.UTF-8排序规则的正常表现：先按不区分大小写的字典序分组排序，组内再区分大小写并结合后续排序字段细化顺序。

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