Example of Recursive CTEs for Working with Hierarchies

Imagine this: you’ve got an online store with thousands of products, all neatly organized into shelves — categories, subcategories, sub-subcategories. On the website, it looks like a slick dropdown menu, but in your database, it’s a headache. How do you pull the whole branch "Electronics → Smartphones → Accessories" in one query? How do you count how many nesting levels each category has? Regular JOINs just don’t cut it — you need recursion!

Building a Product Category Structure with Recursive CTEs

One of the classic tasks in relational databases is working with hierarchical structures. Imagine you have a product category tree: main categories, subcategories, sub-subcategories, and so on. For example:

Electronics
  └── Smartphones
      └── Accessories
  └── Laptops
      └── Gaming
  └── Photo and Video

This structure is easy to show in online store interfaces, but how do you store and fetch it from your database? That’s where recursive CTEs come to the rescue!

Initial Categories Table

First, let’s create a categories table to store product category data:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Unique category identifier
    category_name TEXT NOT NULL,          -- Category name
    parent_category_id INT                -- Parent category (NULL for main categories)
);

Here’s some sample data we’ll add to the table:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Electronics', NULL),
    ('Smartphones', 1),
    ('Accessories', 2),
    ('Laptops', 1),
    ('Gaming', 4),
    ('Photo and Video', 1);

What’s going on here:

• Electronics — this is a main category (no parent, parent_category_id = NULL).
• Smartphones are inside the Electronics category.
• Accessories belong to the Smartphones category.
• The rest of the categories work the same way.

The current data structure in the categories table looks like this:

Building a Category Tree with a Recursive CTE

Now we want to get the whole category hierarchy with nesting levels. For that, we’ll use a recursive CTE.

WITH RECURSIVE category_tree AS (
    -- Base query: select all root categories (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- First nesting level
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Recursive query: find subcategories for each category
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- Increase nesting level
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Final query: get results from the CTE
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Result:

What’s happening here?

1. First, the base query (SELECT … FROM categories WHERE parent_category_id IS NULL) selects the main categories. In this case, it’s just Electronics with depth = 1.
2. Then the recursive query with INNER JOIN adds subcategories, bumping up the nesting level (depth + 1).
3. This process repeats until all subcategories for all levels are found.

Useful Tweaks

The basic example works, but in real projects you usually need more. Say you’re building breadcrumbs for your site or want to show a manager which category has the most subcategories. Let’s check out a few practical upgrades to our query.

1. Adding the Full Category Path

Sometimes it’s handy to show the full path to a category, like: Electronics > Smartphones > Accessories. You can do this with string aggregation:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- concatenate strings
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Result:

Now every category has a full path showing its nesting.

2. Counting the Number of Subcategories

What if you want to know how many subcategories each category has?

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

Result:

The table shows that Electronics has 3 subcategories (Smartphones, Laptops, Photo and Video), and Smartphones and Laptops each have one.

Things to Watch Out For and Common Mistakes with Recursive CTEs

Infinite recursion: If your data has cycles (like a category pointing to itself), your query can go into an infinite loop. To avoid this, you can limit recursion depth with WHERE depth < N or use limits.

Performance tuning: Recursive CTEs can be slow on big datasets. Add indexes on parent_category_id to speed things up.

Mistake: UNION instead of UNION ALL: Always use UNION ALL for recursive CTEs, otherwise PostgreSQL will try to remove duplicates, which slows down your query.

This example shows how recursive CTEs help you work with hierarchical structures. Being able to pull hierarchies from your database will come in handy in tons of real-world projects. For example, building site menus, analyzing org charts, or working with graphs. Now you’re ready to tackle any challenge!

2

Task

SQL SELF, level 27, lesson 4

Locked

Building the full path for each category

Building the full path for each category

Open

1

Survey/quiz

Intro to CTE, level 27, lesson 4

Unavailable

Intro to CTE

Intro to CTE

Open