Tree example with PostgreSQL ltree

gistfile1.txt

create extension if not exists ltree;
create table tree( id serial primary key, letter char, path ltree );
insert into tree (letter, path) values ('A', 'A'), ('B', 'A.B'), ('C', 'A.C'), ('D', 'A.C.D'), ('E', 'A.C.E'), ('F', 'A.C.F'), ('G', 'A.B.G');

with recursive tree_hierarchy as (
    select 
	    id, letter, path, 1 as level, letter::text as full_path
    from tree
    where nlevel(path) = 1 -- start from the root node(s)
    union all -- union
    select 
	    t.id, t.letter, t.path, th.level + 1,
        th.full_path || '->' || t.letter as full_path
    from tree t
    inner join tree_hierarchy th on t.path <@ th.path 
      and nlevel(t.path) = nlevel(th.path) + 1
)
select full_path
from tree_hierarchy
order by path;

select id, letter
from tree
where nlevel(path) = 1;

-- All categories level 2
select letter 
from tree where nlevel(path) = 2
order by letter;

-- Main categorie(s) plus count sub categories 
select t1.*, count(t2.*) as descendant_count
from tree t1
left join tree t2 on t2.path <@ t1.path and t2.path != t1.path 
where nlevel(t1.path) = 1
group by t1.id;