CODEWITHSUNDEEP
postgresqlltree
Phelodas
Phelodas

Reputation: 4183

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

Upvotes: 3

Views: 3156

Answers (2)

allenjom
allenjom

Reputation: 137

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.)

Upvotes: 0

Evan Carroll
Evan Carroll

Reputation: 1

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)

Upvotes: 6

Related Questions