PostgreSQL中含小数的文本排序Collation需求

需求：对结构不规整的产品名称执行自然排序——当文本前缀相同时，按文本中包含的数值（整数、小数均可）排序，希望通过PostgreSQL的collation实现。

解决方案：创建ICU-based自然排序规则 #

PostgreSQL默认没有内置的自然排序collation，但可以基于ICU（International Components for Unicode）创建自定义排序规则，ICU支持按数值大小比较字符串中的数字部分，完美适配需求。

步骤1：确认ICU支持 #

首先检查你的PostgreSQL是否启用了ICU支持：

SELECT name, setting FROM pg_settings WHERE name = 'icu_version';

如果返回非空结果，说明已启用ICU；若未启用，需要重新编译PostgreSQL并添加--with-icu参数。

步骤2：创建自定义自然排序规则 #

执行以下SQL创建名为natural的collation：

CREATE COLLATION natural (
    provider = icu,
    locale = 'und-u-kn-true',
    deterministic = true
);

• provider = icu：指定使用ICU作为排序规则提供方
• locale = 'und-u-kn-true'：und是通用区域设置，u-kn-true开启数字自然排序，即字符串中的数字会按数值大小比较，而非字符顺序
• deterministic = true：确保排序结果稳定可预测

示例验证 #

基础示例 #

执行你提供的基础查询，添加COLLATE "natural"：

SELECT col FROM (
    VALUES ('test 0.10'), 
           ('test 0.05'), 
           ('test 0.200'), 
           ('test 5'), 
           ('test 20'), 
           ('test 0.3')) 
AS t(col) ORDER BY col COLLATE "natural";

返回结果符合预期：

col     
────────────
 test 0.05
 test 0.10
 test 0.200
 test 0.3
 test 5
 test 20

复杂场景示例 #

对更贴近实际的产品名称排序，同样使用该collation：

SELECT  col
FROM (
    VALUES ('apple 0.10'),
           ('banana 0.05'),
           ('apple 0.200'),
           ('banana 5'),
           ('apple 20'),
           ('apple 3'),
           ('banana 0.3 or 4.5?'),
           ('flour 100g, 20 bags'),
           ('flour 100g, 3 bags'),
           ('no number'))
AS t(col)
ORDER BY col COLLATE "natural";

返回结果与期望一致：

apple 0.10
 apple 0.200
 apple 3
 apple 20
 banana 0.05
 banana 0.3 or 4.5?
 banana 5
 flour 100g, 3 bags
 flour 100g, 20 bags
 no number

说明 #

• 该collation会自动识别字符串中的整数、小数数值，按数值大小排序，无需额外提取数字字段
• 多数字场景下，会按字符串中数字出现的顺序依次比较（示例中banana 0.3 or 4.5?因第一个数字0.3小于5，排在banana 5之前）
• 无数字的字符串会按字符顺序排在对应前缀组之后

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