Postgres 12本地与AWS RDS环境ORDER BY查询排序结果不一致问题

排序结果不一致是两个环境的排序规则（Collation）配置不统一导致的，和ORDER BY写法无关。
PostgreSQL的字符串排序逻辑不依赖SQL语句单独配置，排序规则优先级从高到低为：查询显式指定 > 字段级配置 > 数据库级配置 > 实例级初始化配置。不同排序规则对非字母字符（比如你这两个值里的.）的排序权重判定差异极大，这也是你看到两个值顺序颠倒的直接原因：

• 若使用en_US.UTF-8、zh_CN.UTF-8这类本地化排序规则，会给标点符号极低的权重，对比时甚至会直接跳过标点，对齐后续字母继续比较，很容易出现和直觉不符的排序结果
• 若使用C排序规则（按字符ASCII码逐位对比），会严格按照每个字符的编码值排序，结果完全确定不受本地化配置影响

你之前尝试用ORDER BY lower(name)没有效果，是因为lower()仅做大小写转换，排序时仍然沿用当前环境的默认排序规则，没有解决规则不统一的核心问题。

分别在两个环境执行以下SQL，即可定位配置差异点：

1. 检查数据库和会话级默认排序规则

-- 查询当前数据库默认排序规则
SELECT datcollate FROM pg_database WHERE datname = current_database();
-- 查询当前会话生效的排序规则
SHOW lc_collate;

2. 检查目标字段单独配置的排序规则

SELECT collation_name
FROM information_schema.columns
WHERE table_name = 'my_table' AND column_name = 'name';

只要两个环境返回的任意一项collation值不一致，就会出现排序结果差异。

你只需要保证两个环境排序规则统一即可，不需要强制要求某一种排序顺序，可选方案按改动成本从低到高排序：

• 查询层显式指定排序规则（改动最小，无需改表/改实例配置）
  直接在ORDER BY子句中强制指定统一的collation，所有环境执行时都会忽略默认配置，按指定规则排序，结果完全一致，推荐优先用这个方案：

-- 示例：强制按C规则排序，结果稳定无环境差异
SELECT name FROM my_table WHERE (...) ORDER BY name COLLATE "C";
-- 如果需要忽略大小写排序，叠加collation即可
SELECT name FROM my_table WHERE (...) ORDER BY lower(name) COLLATE "C";

• 统一字段级排序规则
  如果不想每次写查询都加collation，可以把两个环境的name字段collation改成同一个值。注意修改字段类型会触发该字段上的UNIQUE约束重建，大表场景需要预留维护窗口：

-- 示例：将字段排序规则修改为"C"
ALTER TABLE my_table ALTER COLUMN name TYPE varchar(255) COLLATE "C";

• 实例/数据库级统一配置
  如果是新建环境，初始化PostgreSQL实例、创建数据库时直接指定和现有环境完全一致的lc_collate、lc_ctype参数，从根源避免配置差异。注意已经创建完成的数据库无法直接修改默认collation，需要导出数据后重新建库再导入才能生效。

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