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