PostgreSQL 9.6与12同查询排序结果不同，求统一方案

问题场景 #

执行以下查询语句：

SELECT current_database(),table_name
  FROM information_schema.tables
 WHERE table_type = 'BASE TABLE'
   AND table_schema NOT IN ('pg_catalog', 'information_schema') 
ORDER BY table_name;

• PostgreSQL 9.6输出结果：

  current_database |          table_name          
  ------------------+------------------------------
  mydb              |key_request_sum
  mydb              |key_req_user
  

• PostgreSQL 12输出结果：

  current_database  |          table_name          
  ------------------+------------------------------    
  mydb              |key_req_user
  mydb              |key_request_sum
  
  

原因分析 #

PostgreSQL不同版本中，字符串排序依赖的**排序规则（Collation）**默认值存在差异。早期版本（如9.6）通常默认使用C排序规则（按字节值排序），而较新版本（如12）可能默认采用系统locale对应的字典序排序规则，导致相同字符串的排序结果出现差异。

修复方法 #

修改查询的ORDER BY子句，明确指定与PostgreSQL 9.6一致的C排序规则，强制按字节值排序：

SELECT current_database(),table_name
  FROM information_schema.tables
 WHERE table_type = 'BASE TABLE'
   AND table_schema NOT IN ('pg_catalog', 'information_schema') 
ORDER BY table_name COLLATE "C";

修改后，PostgreSQL 12的查询结果排序会与9.6版本完全一致。

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