Search code examples
postgresqlltree

Collect all leaf nodes with Postgres ltree

I have been using Postgres ltree construct to store a hierarchical structure. Now, I want to collect all the leaf nodes in the tree. Is there a straightforward mechanism to do this? 

CREATE TABLE foo
AS
  SELECT node::ltree
  FROM ( VALUES
    ('Top.Astronomy'),
    ('Top.Astronomy.Astrophysics'),
    ('Top.Pictures'),
    ('Top.Pictures.Stars')
  ) AS t(node);

How do I return

Top.Astronomy.Astrophysics
Top.Pictures.Stars
Solution

  • Using @>

    One way is to use the contains operator @>

    SELECT *
FROM foo AS f1
WHERE NOT EXISTS (
  SELECT *
  FROM foo AS f2
  WHERE f1.node @> f2.node
    AND f1.node <> f2.node
);
            node            
----------------------------
 Top.Astronomy.Astrophysics
 Top.Pictures.Stars
(2 rows)