我有一个包含jsonb列的表,并且我希望将每条记录迁移到新的模式.
旧方案:{"foo": "bar"}
新计划:{"foo": {"value": "bar"}
bar
值因行而异,我希望保留该值.我需要一些方法来从每条记录中查找foo的值.
WITH data AS (
SELECT id AS id, col -> 'foo' AS foo
FROM t
) UPDATE t SET col = jsonb_set('{"foo": {"value": ""}}', '{foo,value}', SELECT foo FROM data WHERE data.id = t.id);
我在Postgres 11.21中得到了这个错误.
ERROR: syntax error at or near "SELECT"
LINE 4: ...jsonb_set('{"foo": {"value": ""}', '{foo,value}', SELECT foo...