where
можно убрать, изменив первое условие на where parent_id = 8
:with recursive r as (
select
id, parent_id, name
from catalog_category
where parent_id = 8
union
select
cc.id, cc.parent_id, cc.name
from catalog_category as cc
join r
on cc.parent_id = r.id
)
select id, name from r;