PostgreSQL中如何将TO_NUMBER()无效格式错误处理为NULL实现数值排序

该需求可实现，无需修改表结构，有三类可落地的实现方案：

全版本兼容：自定义带异常捕获的转换函数 #

原生TO_NUMBER遇到非法输入会直接抛出异常中断查询。可以封装PL/pgSQL函数，捕获转换过程中的所有异常，异常场景统一返回NULL：

CREATE OR REPLACE FUNCTION safe_to_number(input_text text, format_text text DEFAULT '0000')
RETURNS numeric AS $$
BEGIN
  RETURN TO_NUMBER(input_text, format_text);
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

替换原有查询中的转换函数即可生效：

SELECT * FROM things ORDER BY safe_to_number(num, '0000');

转换失败的字段会以NULL值参与排序，遵循PostgreSQL默认的NULL排序规则，也可以搭配NULLS FIRST/NULLS LAST自定义NULL值的排列位置。

无自定义函数：正则预校验后转换 #

如果不想创建持久化的自定义函数，可以在排序逻辑中先通过正则校验字符串是否为合法数字格式，校验通过再执行转换，不通过直接返回NULL：

SELECT * FROM things
ORDER BY CASE
  WHEN num ~ '^[0-9]+$' THEN TO_NUMBER(num, '0000')
  ELSE NULL
END;

该方案无异常捕获的额外开销，性能更优。针对用户提到的全空格、字母、特殊符号类无效值，正则会直接过滤并返回NULL，不会触发后续的转换报错。如果需要兼容带前后空格的合法数字、带正负号的数字，可将正则调整为^\s*-?[0-9]+\s*$。
该方案的缺点是：如果数字格式复杂（比如带千分位分隔符、小数位），需要严格匹配正则规则，否则容易漏判合法输入。

高版本原生支持：TRY_CAST语法 #

PostgreSQL 16及以上版本内置了TRY_CAST表达式，转换失败时自动返回NULL，对于纯整数场景可以直接使用：

SELECT * FROM things ORDER BY TRY_CAST(num AS integer);

该语法无需自定义函数、无需正则校验，是高版本下最简洁的实现。但该语法不支持自定义数字格式参数，如果需要解析带固定格式、千分位分隔符的数字，仍需结合前面两个方案实现。

注意事项 #

• 直接使用num::numeric的写法没有容错能力，遇到非法输入同样会抛出转换错误
• 自定义函数标记为IMMUTABLE属性后，优化器可对函数计算结果做缓存，大幅提升大数据量下的排序性能
• 排序时可通过NULLS FIRST/NULLS LAST调整无效值的排列位置：PostgreSQL默认升序排序时，NULL值排在所有有效数值的末尾

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