SQL Recursive CTE Generator (Tree and Hierarchy Queries)
Categories, org charts, comment threads and folder trees are usually stored as an adjacency list: each row has an id and a parent_id. Reading such a tree takes a recursive common table expression, and writing one by hand means getting several details right at once: an anchor query, a recursive step joined on the parent column, a depth counter, a path for sorting the output as a tree, and a guard so that a bad row (A is the parent of B and B the parent of A) cannot make the query run forever. This generator writes the whole query for PostgreSQL or SQLite in four shapes: every descendant of a node, the ancestor chain from a node up to the root, the full tree with indentation and breadcrumbs in display order, and a check that lists the rows caught in loops. You choose how roots are marked (NULL parent, parent 0, or a row that is its own parent), a maximum depth, sibling order, and the cycle guard: an id path in both databases or the CYCLE clause of PostgreSQL 14+. Every shape was compared with a reference implementation on random forests containing loops, on PostgreSQL 18.7 and SQLite 3.51. Everything runs in your browser.
- Siblings are sorted by name; rows with the same name under one parent come out in no fixed order.
- The path array stops the recursion when an id repeats, so loops in the data cannot make the query run forever.
- An index on parent_id makes each recursive step an index lookup instead of a full scan: CREATE INDEX ON categories (parent_id);
Background reading: PostgreSQL Recursive CTE (WITH RECURSIVE) Guide and ltree for Hierarchical Data in PostgreSQL.
Do more than sql recursive cte generator (tree and hierarchy queries) — meet Chat2DB
Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.
How to use
- Choose PostgreSQL or SQLite and enter the table, the id column, the parent column and an optional label column such as name.
- Pick the query: descendants of a node, ancestors of a node, the full tree, or a loop check, and set how root rows are marked and which cycle guard to use.
- Copy the WITH RECURSIVE query and run it in psql, sqlite3 or Chat2DB; add an index on the parent column for large tables.
Frequently asked questions
How do I get all children of a node at every level in SQL?
Use a recursive CTE. The anchor selects the start row (WHERE id = 42), and the recursive part joins the table to the CTE on child.parent_id = cte.id, so each round adds the next level of children until a round finds nothing. Carry a depth column (0 in the anchor, depth + 1 in the recursive part) to know how deep each row sits, and filter depth > 0 if you want the descendants without the start row itself. The same query works in PostgreSQL and SQLite with WITH RECURSIVE; only the cycle guard and the string functions differ.
How do I stop a recursive CTE from looping forever?
Keep the ids visited so far and refuse to visit one again. In PostgreSQL that is an array column: ARRAY[id] in the anchor, path || child.id in the recursive part, and WHERE child.id <> ALL (path). PostgreSQL 14 and later can do the same with CYCLE id SET is_cycle USING cycle_path after the CTE. SQLite has no arrays, so the generator keeps a string like ',1,4,9,' and checks instr(path, ',' || id || ',') = 0. A UNION instead of UNION ALL only stops loops when entire rows repeat, and with a depth column they never do, so it is not a reliable guard.
How do I print a hierarchy as an indented tree in the right order?
Build a sort key while you recurse: an array of ids or names in PostgreSQL (ARRAY[name] then sort_key || child.name), or a string in SQLite. ORDER BY sort_key then puts every child directly under its parent, and siblings in id or name order. For the indentation, prefix the label with repeat(' ', depth) in PostgreSQL, or use printf('%*s%s', depth * 2, '', name) in SQLite (a separate printf('%*s', 0, '') returns NULL in SQLite, which would blank the root rows). The breadcrumb column (Electronics > Laptops > Gaming) comes from concatenating the labels along the way. You can run the generated query in Chat2DB (https://chat2db.ai/download or https://app.chat2db.ai) and browse the result as a grid.
