By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE a (
comments text
);
INSERT INTO a
VALUES
('{"test1":null,"test2":99}'),
('{"test3": 1}'),
('{"test2": 20, "test3": 2}');
CREATE TABLE
INSERT 0 3
SELECT * FROM a
| comments |
|---|
| {"test1":null,"test2":99} |
| {"test3": 1} |
| {"test2": 20, "test3": 2} |
SELECT 3
do $$
declare
commentKey text;
commentKeys text[] := array['test1','test2'];
begin
FOREACH commentKey IN ARRAY commentKeys
loop
UPDATE a
SET comments = jsonb_set(to_jsonb(comments), ('{'||commentKey||'}')::text[], to_jsonb(round(((to_json(comments) ->> commentKey)::numeric) / 100, 6)))
WHERE comments::jsonb ->> commentKey is not null;
end loop;
end;
$$ language plpgsql;
DO
SELECT *
FROM a
| comments |
|---|
| {"test3": 1} |
| null |
| null |
SELECT 3