Postgres 11

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

create table domains(
    id bigint,
    domain_name varchar,
    parent_id bigint,
    valid boolean
)

CREATE TABLE


insert into domains values
    (1, 'example.com', null, false),
    (2, 'a.example.com', 1, true),
    (3, 'b.example.com', 1, false),
    (4, 'c.b.example.com', 3, true),
    (5, 'd.a.example.com', 2, true)

INSERT 0 5


WITH RECURSIVE cte AS (
  SELECT id, domain_name, valid 
  FROM domains
  WHERE parent_id IS NULL
UNION ALL
  SELECT domains.id, domains.domain_name, domains.valid 
  FROM domains
  JOIN cte ON domains.parent_id = cte.id
  WHERE NOT cte.valid
)

SELECT *
FROM cte
WHERE valid


id
domain_name
valid


2
a.example.com
t

4
c.b.example.com
t



SELECT 2