Postgres中将JSONB提取的含空值、空串的price文本转为numeric(10,2)方法咨询

解决方案 #

你可以通过nullif + coalesce组合简化空值、空字符串的判断逻辑，同时显式指定numeric的精度和标度即可得到你要的numeric(10,2)类型，简化写法如下：

select 
  coalesce(nullif(t.config::jsonb ->> 'price', ''), '0')::numeric(10,2) as price 
from mytable t

逻辑说明

1. nullif(t.config::jsonb ->> 'price', '')：将提取到的空字符串转换为null，此时结果仅包含null和合法价格文本两类值
2. coalesce(..., '0')：将所有null值统一替换为文本类型的0
3. 末尾显式指定::numeric(10,2)强制转换为你需要的精度类型，原方案没有指定精度，Postgres会默认使用无约束的numeric类型，才会出现numeric(131089,0)的返回结果

如果你的业务场景中可能存在price字段存储非数字格式文本的情况，可以额外增加格式校验逻辑避免转换报错。

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