PostgreSQL排序时为何将波浪线(~)视为空格？需调整设置吗？

问题场景 #

创建表结构：

CREATE TABLE "SOMESCHEMA".newtable (
    col1 varchar NULL
);

插入三行数据：

"A 1"
"A~2"
"A 3"

执行降序查询：

SELECT col1 FROM "SOMESCHEMA".newtable ORDER BY col1 desc;

得到结果：

"A 3"
"A~2"
"A 1"

疑问：为何"A~2"既不在最上方也不在最下方？当前列使用en_US.UTF-8排序规则，是否需要调整设置？

原因解析 #

这个排序结果是en_US.UTF-8排序规则的正常表现，核心原因在于该规则对不同字符的权重定义：

• 在en_US.UTF-8的排序逻辑中，空格属于低权重可忽略字符，排序时会优先跳过空格，比较后续的有效字符；
• 数字的排序权重高于波浪线（~），而波浪线的权重又高于数字1。

具体比较过程：

1. **"A 3" vs "A_{2"**：跳过空格后，实际比较"A3"和"A}2"。数字3的权重高于波浪线，因此"A 3" > "A~2"，降序时排在前面；
2. "A~2" vs "A 1"：跳过空格后，实际比较"A_{2"和"A1"。波浪线的权重高于数字1，因此"A}2" > "A 1"，降序时排在中间。

调整方案 #

如果需要改变排序逻辑，可通过以下方式调整：

1. 使用二进制排序（ASCII码顺序） #

若希望按字符的二进制ASCII码值排序，可在查询时临时指定C排序规则，或修改列的默认排序规则：

• 查询时临时指定：

SELECT col1 FROM "SOMESCHEMA".newtable ORDER BY col1 COLLATE "C" DESC;

此时排序结果会是："A~2"、"A 3"、"A 1"（因为ASCII码中~的数值远大于空格和数字）。

• 修改列的默认排序规则：

ALTER TABLE "SOMESCHEMA".newtable ALTER COLUMN col1 TYPE varchar COLLATE "C";

2. 自定义排序规则（进阶） #

如果需要更个性化的排序逻辑，可创建自定义排序规则，但需要依赖系统支持的locale工具（如ICU），操作相对复杂，一般推荐优先使用现有内置排序规则。

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