Postgres get root of self-referential table

Given the table:

CREATE TABLE self-ref (
  name VARCHAR NOT NULL,
  parent VARCHAR REFERENCES self-ref(name),

  PRIMARY KEY(name)
);

If I get a name from the table (and thus the Pkey), how can I get the name of it’s “root”-level parent: the name where parent is NULL which is referenced to (indirectly) by the given name.

Say I have these values (I don’t know which values I will get):

+-------------+-----------+
| Name        | Parent    |
+-------------+-----------+
| Root 1      | NULL      |
| Root 2      | NULL      |
| Child 1     | Root 1    |
| Child 2     | Root 2    |
| Sub-child 1 | Child 1   |
+-------------+-----------+

The function/whatever solution should return Root 1 for Root 1, Child 1 and Sub-child 1; and should return Root 2 for Root 2 and Child 2.

How can I achieve this? Note: It really has to be Postgres….

Answer

You need a recursive CTE. The syntax looks like:

with recursive cte as (
      select name, name as parent
      from selfref
      where parent is null
      union all
      select s.name, cte.parent
      from cte join
           selfref s
           on s.parent = cte.name
     )
select *
from cte;

Here is a db<>fiddle.