我相对较新,希望在当前数据库中重新指定一些值。这意味着进入数据库中的jasonb列,选择一个键值并将其除以1000。我知道如何选择值,但在执行计算后更新失败了。我的表名为property_calculation,有两列,如下所示:*dynamic_fields是我的jasonb列
ID | dynamic_fields |
---|---|
1 | {"总基本值":4198571.23072064584484186513013602874778211116790771484375,"表面面积值":18.108285497586717127660449477843940258026123046875,"评估年值":1801819.53479890860393683440096079366110007766166312137556815287098853607777375} |
2 | {"总基本值":7406547.2893983791876317823721313040007946109771728515625,"表面面积值":31.94416993248973568597648409195244312286376953125,"评估年值":9121964.02268159244211621662122204269151112210677018401838722638785839080546875} |
将jsonb_typeof()
检查移到where子句中:
update property_calculation
set dynamic_fields =
jsonb_set(
dynamic_fields,
'{totalBaseValue}',
to_jsonb((dynamic_fields->>'totalBaseValue')::numeric / 1000)
)
where jsonb_typeof(dynamic_fields->'totalBaseValue') = 'number';
db<gt;小提琴这里