PostgreSQL使用cast转varchar为numeric时报invalid input syntax错误如何解决

• 你写的SQL执行逻辑顺序错误：CASE判断中你先执行了cast(duration as decimal)转换操作，再判断转换结果是否不等于空字符串。但你的duration字段存在空字符串记录，空字符串本身不符合numeric/decimal等数值类型的格式要求，转换步骤直接触发报错，后续的判断逻辑根本不会执行。
• 额外可能原因：duration字段除了空字符串外，还可能存在空格、非数值字符等脏数据，也会导致类型转换失败。

你可以根据你的PostgreSQL版本选择以下任意一种方案：

方案1：调整判断顺序，先过滤非法值再转换（全版本兼容） #

如果你的统计逻辑只需要排除空字符串的记录，不需要实际用到转换后的数值，可以直接先判断duration是否为空，无需提前做类型转换：

select runner_id,
sum(case when trim(duration) <> '' then 1
else 0 end) as delivered, count(order_id) as total_orders
from t_runner_orders
group by runner_id

方案2：用安全转换函数TRY_CAST（PostgreSQL 12及以上版本支持） #

TRY_CAST会在类型转换失败时返回NULL，不会抛出报错，你可以基于返回结果判断是否为有效数值：

select runner_id,
sum(case when try_cast(duration as decimal) is not null then 1
else 0 end) as delivered, count(order_id) as total_orders
from t_runner_orders
group by runner_id

方案3：正则预校验数值格式（低版本兼容） #

如果你的PostgreSQL版本低于12没有TRY_CAST函数，可以用正则表达式先校验字段内容是否为合法十进制数值，再执行转换：

select runner_id,
sum(case when duration ~ '^[0-9]+(\.[0-9]+)?$' then 1
else 0 end) as delivered, count(order_id) as total_orders
from t_runner_orders
group by runner_id

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