sum of recursive tree using Postgres?

Question

I have a tree structure that looks like this:

                                     root
                       A                               B
              A1           A2                     B1        B2
          A1.1  A1.2      A2.1                   B1.1

the table looks something like this:

id | name |value | parent_id
1    root   null    null
2    A      null    1
3    B      null    1    
4    A1     null    2
5    A1.1   2       4
6    A1.2   3       4
.........................

the non leaf nodes must contain the sum of their children leaf nodes.

how can i achieve this with a fast Postgres query

Answer 1

As I comment, If there is only one root node in your table you can just use sum of value

SELECT SUM(value)
FROM T

Or you can try to use CTE RECURSIVE to get which root node you want to SUM

WITH RECURSIVE CTE AS(
 SELECT ID,value,parent_id
 FROM T 
 WHERE value IS NOT NULL
 UNION ALL
 SELECT t1.id,coalesce(t1.value,0) + c.value,t1.parent_id
 FROM CTE c 
 INNER JOIN T t1 ON c.parent_id = t1.id
) 
SELECT id,SUM(value),parent_id
FROM CTE
GROUP BY id,parent_id
order by id

sqlfiddle

Answer 2

update 24-04-2022

Solution with recursive CTE, arrays and ANY()

WITH RECURSIVE r as (
SELECT id
     , name
     , ARRAY[id] as path
     , coalesce(value,0) as value 
  FROM t1
 WHERE name = 'root'
 UNION ALL
SELECT t1.id
     , t1.name
     , array_append(r.path, t1.id) as path
     , r.value + coalesce(t1.value,0) as value
  FROM t1 
 INNER
  JOIN r
    ON t1.parent_id = r.id
)
SELECT id
     , name
     , (select sum(sq.value) from r sq where r.id = any(sq.path) ) as value
  FROM r 

DB Fiddle demo