PostgreSQL中修改JSON列现有字段出现语法错误如何解决

错误原因 #

你编写的语句报错有两个核心问题：

1. 将数值计算逻辑包裹在单引号中，PostgreSQL会将其识别为普通字符串，不会执行实际计算
2. 未处理引号嵌套问题，且jsonb_set的第三个参数要求为jsonb类型，计算后的数值需要显式转换类型后传入

正确实现语句 #

UPDATE my_table 
SET my_column = jsonb_set(
    my_column, 
    '{var2}', 
    to_jsonb((my_column->'var2')::double precision * 100), 
    true
) 
WHERE id = 12;

逻辑说明 #

• 先用->操作符取出var2的原始JSON值，强转为double precision数值类型后执行乘100计算
• 调用to_jsonb()函数将计算结果转为jsonb类型，匹配jsonb_set的参数类型要求
• 不需要将计算逻辑包裹在单引号内，直接作为表达式传入即可

如果需要兼容var2为NULL的场景，避免计算报错，可以使用coalesce函数设置默认值：

UPDATE my_table 
SET my_column = jsonb_set(
    my_column, 
    '{var2}', 
    to_jsonb(coalesce((my_column->'var2')::double precision, 0) * 100), 
    true
) 
WHERE id = 12;

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