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 mytable (
id int,
customer text,
region text,
circle text
);
insert into mytable values
(1, 'airtel', 'tn', 'c1'),
(2, 'reliance', 'ap', 'c2');
2 rows affected
select jsonb_agg(to_jsonb(v)) res
from mytable t
cross join lateral (values
(
concat_ws('_', id::text, customer),
'#',
customer
),
(
concat_ws('_', id::text, customer, region),
concat_ws('_', id::text, customer),
region
),
(
concat_ws('_', id::text, customer, region, circle),
concat_ws('_', id::text, customer, region),
circle
)
) v(id, parent, text)
| res |
|---|
| [{"id": "1_airtel", "text": "airtel", "parent": "#"}, {"id": "1_airtel_tn", "text": "tn", "parent": "1_airtel"}, {"id": "1_airtel_tn_c1", "text": "c1", "parent": "1_airtel_tn"}, {"id": "2_reliance", "text": "reliance", "parent": "#"}, {"id": "2_reliance_ap", "text": "ap", "parent": "2_reliance"}, {"id": "2_reliance_ap_c2", "text": "c2", "parent": "2_reliance_ap"}] |