How to Query a Category Tree in PostgreSQL with a Recursive CTE

In a PostgreSQL category table, each category can store its parent’s ID in the same table. To get a category and all the categories below it, PostgreSQL follows these parent-child links with a recursive common table expression, or recursive CTE. A CTE is a named query that another part of the SQL statement can use. A recursive CTE runs a query repeatedly, using the rows it found in the previous round.

The query first selects starting categories, then joins each result to its children. The query below starts with every root category and returns its descendants, along with each category’s depth.

Store each parent link in the category table

An adjacency list stores a tree by giving each row a reference to its parent. In this table, each category’s parent_id holds that reference. Root categories have no parent, so their parent_id is NULL.

CREATE TABLE categories (
    id        bigint PRIMARY KEY,
    parent_id bigint REFERENCES categories(id),
    name      text NOT NULL
);

CREATE INDEX categories_parent_id_idx ON categories (parent_id);

The self-referencing foreign key requires every non-null parent_id to point to an existing category. It does not prevent a category from becoming its own ancestor, so the query should account for cycles.

Traverse roots and their descendants

Use WITH RECURSIVE to define the CTE. The first SELECT, called the anchor term, chooses the starting rows. The second SELECT, called the recursive term, joins each row found so far to its children. PostgreSQL repeats this join until it finds no more rows. This structure is the standard pattern for an adjacency-list traversal.

WITH RECURSIVE category_tree (id, name, parent_id, depth) AS (
    -- Anchor: start with every root category.
    SELECT c.id, c.name, c.parent_id, 0
    FROM categories AS c
    WHERE c.parent_id IS NULL

    UNION ALL

    -- Recursive term: find each current category's children.
    SELECT child.id, child.name, child.parent_id, tree.depth + 1
    FROM categories AS child
    JOIN category_tree AS tree
      ON child.parent_id = tree.id
)
CYCLE id SET is_cycle USING path
SELECT id, name, parent_id, depth
FROM category_tree
WHERE NOT is_cycle
ORDER BY path;

The depth column assigns roots a depth of 0 and increases it by one for each level below them. The CYCLE clause creates path, and ORDER BY path keeps each category near its descendants in the output.

UNION ALL avoids checking each new row against all previously returned rows to remove duplicates. That suits a tree, where each category has one parent and should be reached by one path. PostgreSQL 14 and later support the CYCLE clause, which tracks visited IDs and marks a row when the query finds an ID it has already visited. The NOT is_cycle filter excludes that row, and the clause stops recursion through it. See the cycle-detection syntax.

Start at one category instead of every root

Replace c.parent_id IS NULL in the anchor condition with a parameterized ID to retrieve one category and its descendants:

WHERE c.id = $1

The recursive join stays the same. The result includes the selected category at depth 0, followed by its descendants. To return only descendants, filter the final result with WHERE depth > 0 AND NOT is_cycle.

If the table holds multiple category trees, limit both the anchor and the recursive join to the same tree identifier. This keeps the query from crossing between trees when the schema allows parent links across them.

Handle older PostgreSQL versions and check performance

PostgreSQL versions before 14 do not support the CYCLE clause. Track visited IDs in an array and stop following a branch when the next child already appears there:

WITH RECURSIVE category_tree (id, name, parent_id, depth, path) AS (
    SELECT c.id, c.name, c.parent_id, 0, ARRAY[c.id]
    FROM categories AS c
    WHERE c.parent_id IS NULL

    UNION ALL

    SELECT child.id, child.name, child.parent_id,
           tree.depth + 1, tree.path || child.id
    FROM categories AS child
    JOIN category_tree AS tree
      ON child.parent_id = tree.id
    WHERE NOT (child.id = ANY(tree.path))
)
SELECT id, name, parent_id, depth
FROM category_tree
ORDER BY path;

The path array records the IDs the query has visited along each branch. The condition stops the query from following a branch back to an ID it has already seen.

The index on parent_id helps the query look up children by parent each time it repeats the join. For a large category table, compare the query’s actual performance with the ways your service reads categories. Recursive CTEs simplify traversal, but the cost depends on the data and query.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com