How do I find the leafs using PostgreSQL's ltree?
Using ltree2text sounds like a horrible idea, and cstring is an internal thing -- you should never be using that. If an ltree has no descendants it's a leaf, so let's test for that using @&gt;

CREATE TABLE foo(ltree)
AS
  VALUES
    ( 'Top.Science.Astronomy'::ltree ),
    ( 'Top.Science.Astronomy.Astrophysics' ),
    ( 'Top.Science.Astronomy.Cosmology' ),
    ( 'Top.Collections.Pictures.Astronomy' ),
    ( 'Top.Collections.Pictures.Astronomy.Stars' ),
    ( 'Top.Collections.Pictures.Astronomy.Galaxies' ),
    ( 'Top.Collections.Pictures.Astronomy.Astronauts' );

CREATE INDEX ON foo USING gist(ltree);

SELECT *
FROM foo AS f1
WHERE NOT EXISTS (
  SELECT 1
  FROM foo AS f2
  WHERE f1.ltree &lt;&gt; f2.ltree
    AND f1.ltree @&gt; f2.ltree
);


This uses the GIST index on the ltree. See the docs for more information on using @&gt; with ltree.

I suggest not storing this on the table. First, it's maintenance nightmare. After every insert and update, you would have to potentially rewrite the parent nodes to update the leaf column. Moreover, if simply querying for this isn't fast enough, I would instead use a MATERIALIZED VIEW. At least then you know when the view is stale. Keeping this in sync with your data sounds like no-fun to me.