Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE selfref (
  name VARCHAR NOT NULL,
  parent VARCHAR REFERENCES selfref(name),

  PRIMARY KEY(name)
);

CREATE TABLE


insert into selfref (name, parent)
VALUES
('Root 1', NULL),
('Root 2', NULL),
('Child 1', 'Root 1'), 
('Child 2', 'Root 2'), 
('Sub-child 1', 'Child 1')

INSERT 0 5


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


name
parent


Root 1
Root 1

Root 2
Root 2

Child 1
Root 1

Child 2
Root 2

Sub-child 1
Root 1



SELECT 5

name	parent
Root 1	Root 1
Root 2	Root 2
Child 1	Root 1
Child 2	Root 2
Sub-child 1	Root 1