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?

Gilroy

I haven't found any direct support for this within ltree, but you can select the child node with path ~ '*.C' & then you can do a self-join to get all ancestors. – Benisch

Thanks, I'm gonna try it too. – Ingraham

How do you know "C", but not the full path "A.B.C"? Aren't they on the same row of the table? – Verdugo

@KouberSaparev yes they are, but "C" is a kind of global identifier for that row, which I receive as a parameter in this case. – Ingraham

Then with a subselect you can obtain the path corresponding to the identifier and search by it (as in the answer proposed below). – Verdugo

Answer 1

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

Verdugo

Jesus that was quite simple. It's far too many hours now whilte I'm sitting at the computer :) By the time I managed to implement it with a self-join, but the subquery in this case is much more readable. Thank you. – Ingraham

Thanks for good answer! But what if sub-select returns more than one row? It could happen if some node has more than one parent. Then Postgres shows error: "more than one row returned by a subquery used as an expression" Are there a way to handle this? – Fiver

How would you define a node with more than one parent at all with the materialized path pattern that ltree uses? – Verdugo

Definitely possible for a node to have more than one parent in a DAG. In that case @Johnner, it's better to rewrite as a JOIN, not a subquery: SELECT * FROM myTree JOIN myTree AS mt ON myTree.path @> mt.path AND mt.label = 'C'; – Bedsore

In my opinion, the answer by @Kouber Saparev assumes that there is a "label" column in the table. I think the following can be used: select * from myTree where path ~ '*.C'; As per the documentation here, *.foo matches any label path whose last label is foo. This seems trivial and I am thinking I might be misunderstanding something. Please correct me if I am wrong somewhere. Thank you, – Sensible

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.

Parade

Thank you, this looks quite tricky for my current knowledge, but will give it a try. What I'm afraid of is that (I think) I'm limited to simple query statements because I'm behind an ORM library. – Ingraham