PostgreSQL Recursive CTE

Summary: in this tutorial, you’ll learn how to use the PostgreSQL recursive CTE to query hierarchical data such as organization charts and category trees.

Getting Started with the PostgreSQL Recursive CTE #

CTE provides a way to define a temporary table within a query. A recursive CTE is a type of CTE that references itself in its CTE query definition.

In programming, a recursive function is a function that calls itself until it doesn’t. Similarly, a recursive CTE is a CTE that references itself in the CTE query.

A recursive CTE has two main parts:

Anchor member is a query that provides the base result set.
Recursive member is a query referencing the CTE itself. It will execute repeatedly and build up the final result set. The recursive member has a termination condition that stops execution when it returns no row.

A recursive CTE uses UNION or UNION ALL to combine result sets returned by the anchor member and recursive member.

Here’s the syntax of a recursive CTE:

WITH RECURSIVE cte_name (column1, column2, ...) AS (
    -- Anchor member
    SELECT ...
    UNION ALL
    -- Recursive member
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT *
FROM cte_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

The WITH RECURSIVE keyword defines a recursive CTE with a name (cte_name).
An anchor member forms the base result set.
A recursive member takes the base result set and starts the recursion until it returns no rows.
The UNION (or UNION ALL) operator combines the result sets of the anchor member and recursive member into a final result set.

PostgreSQL Recursive CTE example #

The following example uses a recursive CTE to generate a countdown from 3 to 1:

WITH RECURSIVE count_down (counter) AS (
    -- Anchor member
    SELECT  3 AS counter
    UNION
    -- Recursive member
    SELECT counter - 1
    FROM count_down
    WHERE counter > 1
  )
SELECT * FROM count_down;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Here’s the breakdown of the query:

First, the anchor member initializes the counter with 3 (first iteration):

SELECT 3 AS counterCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The anchor member forms a base result set:

 counter
---------
       3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, the recursive member starts with the base result set and decrements the counter by 1:

SELECT counter - 1
FROM count_down
WHERE counter > 1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

After the second iteration, the result set will be:

 counter
---------
       2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

After the third iteration, the counter is 1:

 counter
---------
       1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

When the counter is 1, it will stop the anchor member. The UNION operator combines the result sets of all iterations:

 counter
---------
       3
       2
       1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, the main query retrieves all values from the count_down CTE:

SELECT * FROM count_down;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using a Recursive CTE to Query product categories #

Suppose that we have the following product categories organized in tree structure:

Here’s the table structure:

CREATE TABLE IF NOT EXISTS categories (
  category_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category_name VARCHAR(255) NOT NULL UNIQUE,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES categories (category_id) ON DELETE CASCADE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement uses a recursive CTE to query all categories with their depths:

WITH RECURSIVE category_hierarchy AS (
    SELECT category_id, category_name, parent_id,  0 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.category_id, c.category_name, c.parent_id, s.depth + 1
    FROM categories c
    INNER JOIN category_hierarchy s ON c.parent_id = s.category_id
)
SELECT * FROM category_hierarchy;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_id |   category_name    | parent_id | depth
-------------+--------------------+-----------+-------
           1 | Electronics        |      NULL |     0
           2 | Mobile Devices     |         1 |     1
           7 | Home Entertainment |         1 |     1
          10 | Computers          |         1 |     1
           3 | Smartphones        |         2 |     2
           4 | Tablets            |         2 |     2
           5 | Accessories        |         2 |     2
           6 | Wearables          |         2 |     2
           8 | Televisions        |         7 |     2
           9 | Audio Systems      |         7 |     2
          11 | Laptops            |        10 |     2
          12 | Desktops           |        10 |     2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works.

First, the anchor member returns the top category where the parent_id is NULL with the depth 0:

SELECT category_id, category_name, parent_id,  0 AS depth
FROM categories
WHERE parent_id IS NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, the recursive member returns the categories at the depth + 1 until there are no rows to fetch:

SELECT c.category_id, c.category_name, c.parent_id, s.depth + 1
FROM categories c
INNER JOIN category_hierarchy s ON c.parent_id = s.category_idCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, the UNION operator combines the result sets of all iterations.

Finally, the outer query retrieves data from the CTE:

SELECT * FROM category_hierarchy;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

A recursive CTE has a recursive member that references itself.
Use recursive CTE to query hierarchical data.

Quiz #

Was this tutorial helpful ?