sum of ltree children per level

sql,postgresql,ltree

Here you are: select p.path, sum(r.value) from properties p left join properties sub on sub.path::text like p.path::text||'%' left join reports r on r.property_id=sub.id group by 1; How does it work? For each node called in our query p we are retrieving all its subnodes (including itself) by joining sub. To...

PostgreSQL ltree find all ancestors of a given label (not path)

sql,postgresql,ltree

SELECT * FROM myTree WHERE path @> ( SELECT path FROM myTree WHERE label = 'C' ); ...