Postgres 12

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_json(data json);

insert into a_json values('
{
    "PrimKey1": { "layout": "normal", "col1": "PrimKey1",
    "col2": "sometext", "col3": 9, "col4": ["texxt"] },

    "PrimKey500": { "layout": "normal", "col1": "PrimKey500",
    "col2": "someothertext", "col3": 1, "col4": ["texxtagain"] }}
')
1 rows affected

CREATE TABLE b (
  col1      text PRIMARY KEY,
  col2 text,
  col3 numeric,
  col4 text
);
insert into b(col1, col2, col3, col4)
select j.v ->> 'col1', j.v ->> 'col2', (j.v ->> 'col3')::numeric, j.v ->> 'col4'
from a_json a
cross join lateral json_each(a.data) j(k, v)
2 rows affected

select * from b


col1
col2
col3
col4


PrimKey1
sometext
9
["texxt"]

PrimKey500
someothertext
1
["texxtagain"]

col1	col2	col3	col4
PrimKey1	sometext	9	["texxt"]
PrimKey500	someothertext	1	["texxtagain"]