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 t (id int, data jsonb);
insert into t values
(1, '[
   {"id":"1","status":"test status1","updatedAt":"1571145003"},
   {"id":"2","status":"test status2","updatedAt":"1571145323"}
]');

CREATE TABLE



INSERT 0 1


with ct as
(
    select
        id, ('{' || index-1 || ',updatedAt}')::text[] as path
    from
        t,
        jsonb_array_elements(data) with ordinality arr(c, index)
)
--select * from ct
--update t
--set    data = jsonb_set(data, ct.path, '"1571150000"', false)
--from   ct
--where  ct.id = t.id;

ERROR:  syntax error at end of input
LINE 13: --where  ct.id = t.id;
                               ^


select * from t;


id
data


1
[{"id": "1", "status": "test status1", "updatedAt": "1571145003"}, {"id": "2", "status": "test status2", "updatedAt": "1571145323"}]



SELECT 1


with ct as
(
    select id, jsonb_array_elements(data) dt
    from   t
)
, ct2 as
(
  select id, jsonb_set(dt, '{updatedAt}', '"1571150000"', false) dt2
  from   ct
)
update t
set    data = (select jsonb_agg(dt2) from ct2 where ct2.id = t.id);


UPDATE 1


select * from t;


id
data


1
[{"id": "1", "status": "test status1", "updatedAt": "1571150000"}, {"id": "2", "status": "test status2", "updatedAt": "1571150000"}]



SELECT 1