PostgreSQL与Oracle对字符字段ORDER BY排序结果不一致的解决方法

当前排序结果与预期不符的核心是Oracle与PostgreSQL的字符串排序规则底层逻辑存在差异：

• Oracle 19c 默认采用二进制排序（BINARY），直接按照字符的ASCII码值逐位对比：字符>的ASCII码为62，数字字符的ASCII码范围为48（'0'）-57（'9'），因此带>的字符串在降序排序时，会因为相同前缀下>的码值高于数字字符，排在纯数字字符串前面，无法得到预期结果。
• PostgreSQL 默认采用操作系统本地化的字典排序规则，给数字字符设置了高于标点特殊字符的优先级，因此相同前缀下纯数字字符串的排序权重更高，能输出符合预期的结果。
• 测试用的CHAR(50)是定长字符类型，存储时会自动在字符串末尾补空格，会小幅影响短字符串的排序权重，但不是本次问题的核心诱因。

方案1：指定与PostgreSQL对齐的NLS排序规则（推荐，改动最小） #

无需修改表结构、也无需调整数据库全局参数，直接在ORDER BY子句中通过NLSSORT函数指定匹配的排序规则即可，基础SQL写法：

SELECT * 
FROM test 
ORDER BY NLSSORT(data, 'NLS_SORT=ENGLISH', 'NLS_COMP=LINGUISTIC') DESC;

说明：ENGLISH排序规则的字符优先级为「字母 > 数字 > 标点符号」，和PostgreSQL默认的英文本地化字典排序逻辑完全一致。如果业务为中文场景，可将排序规则替换为ZH_PINYIN（拼音排序），该规则同样将数字优先级设置为高于标点特殊字符，输出结果一致。
因为测试表用了定长CHAR(50)类型，存储时会自动补尾部空格，建议在排序时先去除填充的空格，避免干扰对比逻辑，优化后的SQL：

SELECT * 
FROM test 
ORDER BY NLSSORT(TRIM(data), 'NLS_SORT=ENGLISH', 'NLS_COMP=LINGUISTIC') DESC;

方案2：自定义排序逻辑（不依赖NLS参数，结果完全可控） #

如果担心不同环境下NLS排序规则存在差异，可以直接按照预期的字符优先级自定义排序表达式，保证数字字符排序权重高于特殊字符，同时保留数字本身的大小比较逻辑，写法如下：

SELECT * 
FROM test
ORDER BY 
  -- 逐字符构造排序键：数字字符权重设为1开头，特殊字符权重设为0开头，保证数字优先级高于特殊字符
  REGEXP_REPLACE(
    TRIM(data),
    '(.)',
    CASE WHEN REGEXP_LIKE('\1', '[0-9]') THEN '1\1' ELSE '0\1' END
  ) DESC;

说明：该逻辑完全复刻PostgreSQL的字典排序规则：先按照「数字 > 特殊字符」的优先级对比相同位置的字符，优先级一致时再对比字符本身的大小（比如数字3 > 数字1），相同前缀下更长的字符串优先级更高。执行后输出的排序结果与预期完全一致，且不受数据库全局NLS参数的影响。

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