Postgres 10

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
   update a set comments = (select jsonb_object_agg(k.key, 
       case when k.value#>>'{}' is not null and k.key::text = any(commentKeys) 
              then (k.value::text::float / 100)::text::jsonb 
            else k.value end) 
       from jsonb_each(comments::jsonb) k);
end;
$$ language plpgsql;

DO

SELECT *
FROM a

comments

{"test1": null, "test2": 0.99}

{"test3": 1}

{"test2": 0.2, "test3": 2}

SELECT 3