> On 26 Jul 2021, at 17:19, Avi Weinberg <AviW@xxxxxxxxx> wrote: > > Hi, > > I would like to populate the children_ids column with all the ids of the children recursively (+ grandchildren etc.) > If I do it top-bottom I will end up doing extra work since there is no need to go all levels down if I can just compute my IMMEDIATE children "children_ids" and just concatenate all their lists. (…) > create table tree(id int primary key, parent int, children_ids text); > insert into tree (id, parent) values > (273, 0), > (274, 273), > (275, 273), > (277, 273), > (278, 277), > (280, 275), > (281, 280), > (282, 281), > (283, 282), > (284, 282), > (285, 282), > (286, 282), > (287, 282), > (288, 282), > (289, 282), > (290, 281), > (291, 290), > (292, 290), > (293, 290), > (294, 290), > (295, 290); First you need to figure out what your starting set of nodes is, and since you’re going to go bottom-up, those are your leaf nodes. Without any indicators for that though, you’ll have to determine that from a sub-query. Something like this: with recursive foo (id, parent, children_ids) as ( select id, parent, null::text from tree t where not exists ( select 1 from tree c where c.parent = t.id ) union all select t.id, t.parent , f.id || case f.children_ids when '' then '' else ',’ end || f.children_ids from foo f join tree t on f.parent = t.id where f.parent <> 0 ; Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
