Recursive CTEs — write one to walk a tree of comments.

A recursive CTE (WITH RECURSIVE) repeatedly feeds its own output back into itself until no new rows are produced — the standard tool for walking trees and graphs like a threaded comment hierarchy. Every recursive CTE has two parts joined by UNION [ALL]: a non-recursive anchor (the seed rows) and a recursive term that references the CTE name.

Walking a comment tree

Schema: comments(id, post_id, parent_id, author, body), where top-level comments have parent_id IS NULL.

WITH RECURSIVE thread AS (
  -- anchor: the roots
  SELECT id, parent_id, author, body, 1 AS depth,
         ARRAY[id] AS path
  FROM comments
  WHERE post_id = 100 AND parent_id IS NULL

  UNION ALL

  -- recursive term: children of rows already in `thread`
  SELECT c.id, c.parent_id, c.author, c.body, t.depth + 1,
         t.path || c.id
  FROM comments c
  JOIN thread t ON c.parent_id = t.id
)
SELECT repeat('  ', depth - 1) || body AS indented, author, depth
FROM thread
ORDER BY path;

How it executes

1. Run the anchor once → the root comments, depth = 1.
2. Run the recursive term, joining comments to the rows produced in the previous step, yielding their direct children.
3. Repeat step 2 on each new batch until it returns no rows.
4. UNION ALL accumulates every batch into the final result.

The path array (ARRAY[id] extended with || c.id) records the ancestry, so ORDER BY path renders the tree in proper depth-first, parent-before-child order — and depth drives the indentation.

To find a single comment's ancestors instead, flip the join: seed with the target and recurse on c.id = t.parent_id.