LTREE ancestor query in Postgres not working as expected

Question

I have a table with an LTREE column, and the data is somewhat as described below.

ID  Label  Path
1.  ABC.   1
2.  DEF.   1.2
26. GHI.   1.2.26

Let's suppose I want to find all the ancestors of the node labelled GHI. The query I'm using is

SELECT *
    FROM
        my_table as tbl
    WHERE
        tbl.path @> (
            SELECT
                path 
            FROM
                my_table 
            WHERE
                id=26
        ) 

However, this returns only the last row, whereas I want all the three rows. What am I doing wrong?

Answer 1

I'm sort of a newcomer to Ltree as well, but I think I could help you out!

Your setup should match the GIST index for the Ltree. See the F.21.3 part of the official documentation. https://www.postgresql.org/docs/current/ltree.html

I believe that without it, the operator is unable to properly figure out what you want. Besides that the query seems correct. I ran the same code on my local environment (although I use the latest version of Postgres) which worked.

Answer 2

To find the all descendants for the given path use the <@ operator

SELECT *
FROM my_table as tbl
WHERE path <@ '1.12.26'

For more info https://www.postgresql.org/docs/current/ltree.html#LTREE-OP-TABLE