Postgres 16

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 tbl (id text, values_detail text);
insert into tbl values ('1afd3d7e-d05v-4d63-9cef-8fb9f6f9514f', '{{"name":"MyName","age":10.0, "attribut":{"attack":"20","defence":30},{"name":"yourName","age":20.0}}');

CREATE TABLE



INSERT 0 1


with recursive cte(id, s, v) as (
   select t.id, '', t.values_detail from tbl t
   union all
   select c.id, concat(c.s, case when regexp_substr(c.v, '^\:\d+') is not null then 
       ':'||(regexp_substr(substr(c.v, 2), '^\d+')::int * 2)::text
    when regexp_substr(c.v, '^:"\d+"') is not null then
        ':"'||(regexp_substr(substr(c.v, 3), '^\d+')::int * 2)::text||'"'
    else substr(c.v, 1, 1) end),
    case when regexp_substr(c.v, '^\:\d+') is not null then regexp_replace(c.v, '^\:\d+', '')
    when regexp_substr(c.v, '^:"\d+"') is not null then regexp_replace(c.v, '^:"\d+"', '')
    else substr(c.v, 2) end
  from cte c where length(c.v) > 0
)
update tbl set values_detail = c.s from cte c where c.id = tbl.id and length(c.v) = 0

UPDATE 1


select * from tbl;


id
values_detail


1afd3d7e-d05v-4d63-9cef-8fb9f6f9514f
{{"name":"MyName","age":20.0, "attribut":{"attack":"40","defence":60},{"name":"yourName","age":40.0}}



SELECT 1