Recursive querys in PostgresSQL

bottom-up.sql

with recursive category_ancestors (id, parent_id, name) as (
  select id, parent_id, name
  from categories where id = 123
  union
  select parent.id, parent.parent_id, parent.name
  from categories parent
  inner join category_ancestors on parent.id = category_ancestors.parent_id
)
select * from category_ancestors;

breadth-first.sql

with recursive category_graph as (
  select id, parent_id, name
  from categories where id = 1
  union
  select child.id, child.parent_id, child.name
  from category_graph
  inner join categories as child on child.parent_id = category_graph.id
)
select * from category_graph;

depth-first.sql

with recursive category_graph as (
  select id, parent_id, name, array[id] as path, 1 as depth
  from categories where id = 1
  union
  select child.id, child.parent_id, child.name, array_append(category_graph.path, child.id) as path, category_graph.depth + 1 as depth
  from category_graph 
  inner join categories as child on child.parent_id = category_graph.id
)
select * from category_graph order by path;

slick!