Recursive CTE for a tree

-- Walk an adjacency-list tree from a root node down.
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT id, parent_id, name, depth
FROM tree
ORDER BY depth, name;