PostgreSQL ltree find all ancestors of a given label (not path)

Question

I have a table with an ltree path column. In my hierarchy the path to a label is unique, meaning that every label has exactly one parent label. In other words, there are no two ltree paths in the table which would end with the same label.

I have an ltree label, let's say "C".

I can find all descendant rows of this label with the following query:

select * from myTree where path ~ '*.C.*';

This works fine and gives the correct subtree.

Now I need to implement the query to find all ancestor rows of this given label. I mean if there are for example 3 rows in the table with labels "A", "A.B", "A.B.C", I would like to get the rows with paths "A" and "A.B" (probably including "A.B.C" itself, does not matter for now).

If I know the full path of "C" ("A.B.C" in the above example) then the task is easy with the @> operator. However, now I know only "C", and still I would like to achieve the task with a single query. Is there any way to do this?

Answer 1

SELECT
  *
FROM
  myTree
WHERE
  path @> (
    SELECT
      path
    FROM
      myTree
    WHERE
      label = 'C'
  );

Answer 2

Something like this:

WITH r AS
(
    SELECT *, row_number() OVER (ORDER BY path) rn
      FROM myTree
)
SELECT *
  FROM r
 WHERE rn <= (SELECT rn FROM r WHERE path ~ '*.C');

SQL Fiddle

Maybe there's a better way using built-in stuff though.