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(colA varchar(20), colB int);
insert into t values
('aa' , 1),
('aa' , 2),
('aa.bb' , 3),
('aa.bb.cc' , 4),
('aa.bb.cc' , 5),
('dd' , 6),
('dd.ee' , 7);
7 rows affected
select t.cola, Concat('[',x.colB,']') ColB
from t
left join lateral (
select string_agg(colb::character varying,',') colB
from t t2
where t2.cola like t.cola || '%'
)x on true
group by t.cola, x.colb;
| cola | colb |
|---|---|
| aa | [1,2,3,4,5] |
| aa.bb | [3,4,5] |
| aa.bb.cc | [4,5] |
| dd | [6,7] |
| dd.ee | [7] |