3

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
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
Phelodas
  • 3,853
  • 5
  • 25
  • 30

2 Answers2

5

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)
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
0

If the leaves are always at the third level, do this:

SELECT * FROM foo WHERE node ~ '*{2}.*';

The quantifiers are quite useful. You can also find nodes in the middle of a long branch. To use the PostgreSQL example test table in the docs at https://www.postgresql.org/docs/current/static/ltree.html

SELECT * FROM test WHERE path ~ '*{2}.Astronomy.*{1}';

will match only the 'Astronomy' at the third of a four-length branch.

You could also have another column as a flag to indicate if it's a leaf or not. (BTW the @<> operators require the gist index, and I found it to be significantly slower on a large dataset. I removed it and just use the btree ~ operator. I took it out, and it's working fine, just not needed, I guess.)

allenjom
  • 137
  • 2
  • 5