Oracle与PostgreSQL执行order by排序结果不一致如何永久修复

排序差异原因 #

Oracle的AL32UTF8默认排序逻辑是基于字符的ASCII码值排序，和PostgreSQL的C（POSIX）排序规则完全对齐。你当前PostgreSQL使用的en_US.UTF-8排序规则会忽略空格、括号等特殊符号的优先级，仅按字母内容排序，因此出现和Oracle结果不一致的情况。

永久生效配置方案 #

你可以根据适用范围选择以下任意一种方案：

• 实例级（最彻底，适合全新部署）
  初始化数据库实例时指定排序规则，后续该实例下所有库、表、字段默认都会继承该配置：

  initdb --locale=C --encoding=UTF8 /你的数据目录路径
  

• 库级（适合已有实例，仅对指定库生效）
  对已有数据库修改默认排序规则，修改后所有新连接生效，该库后续新建的对象默认继承该规则：

  ALTER DATABASE 你的数据库名称 SET lc_collate = 'C';
  

• 字段级（适合不修改全局配置，仅针对特定业务字段生效）
  直接修改需要对齐Oracle排序的表字段的排序规则，修改后对该字段的所有排序查询自动生效，不需要每次手动加collate参数：

  -- 示例：修改test表的col1字段排序规则为C，注意替换为你实际的字段类型
  ALTER TABLE test ALTER COLUMN col1 TYPE varchar(200) COLLATE "C";
  

• 会话级（仅对当前连接生效，适合临时调试）
  执行以下SQL后当前会话的所有排序都会默认使用C规则，断开连接后自动失效：

  SET lc_collate = 'C';
  

注意事项 #

1. 修改库或字段排序规则前请务必提前备份数据，避免操作异常导致数据损失
2. 对已有大量数据的字段修改排序规则会触发全表重写，同时会锁表，需要在业务低峰期操作
3. 如果你有更复杂的多语言排序需求，PostgreSQL 10及以上版本支持ICU自定义排序规则，可按需配置使用。

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