PostgreSQL - Recursive Query

PostgreSQL is a powerful relational database management system, widely recognized for its advanced capabilities, one of which is the ability to write recursive queries using the WITH statement. This statement, often referred to as Common Table Expressions (CTEs), allows developers to write complex queries with better readability and reusability. Recursive queries, a subset of CTEs, are particularly useful for working with hierarchical data structures, such as organizational charts, file systems, or any scenario where data is linked in parent-child relationships.

WITH Statement in PostgreSQL

The WITH statement in PostgreSQL provides a way to define temporary result sets (or CTEs) that can be referenced within a larger query. Recursive CTEs are queries that refer back to themselves, making them ideal for handling scenarios involving hierarchical data.

Syntax of Recursive CTE in PostgreSQL

WITH RECURSIVE cte_name AS(
    CTE_query_definition <-- non-recursive term
    UNION [ALL]
    CTE_query definition  <-- recursive term
) SELECT * FROM cte_name;

Let's analyze the above syntax:

• The non-recursive term is a CTE query definition that forms the base result set of the CTE structure.
• The recursive term can be one or more CTE query definitions joined with the non-recursive term through the UNION or UNION ALL operator. The recursive term references the CTE name itself.
• The recursion stops when no rows are returned from the previous iteration.

PostgreSQL Recursive Query Examples

First, we create a sample table using the below commands to perform examples:

CREATE TABLE employees (
    employee_id serial PRIMARY KEY,
    full_name VARCHAR NOT NULL,
    manager_id INT
);
INSERT INTO employees (
    employee_id,
    full_name,
    manager_id
)
VALUES
    (1, 'M.S Dhoni', NULL),
    (2, 'Sachin Tendulkar', 1),
    (3, 'R. Sharma', 1),
    (4, 'S. Raina', 1),
    (5, 'B. Kumar', 1),
    (6, 'Y. Singh', 2),
    (7, 'Virender Sehwag ', 2),
    (8, 'Ajinkya Rahane', 2),
    (9, 'Shikhar Dhawan', 2),
    (10, 'Mohammed Shami', 3),
    (11, 'Shreyas Iyer', 3),
    (12, 'Mayank Agarwal', 3),
    (13, 'K. L. Rahul', 3),
    (14, 'Hardik Pandya', 4),
    (15, 'Dinesh Karthik', 4),
    (16, 'Jasprit Bumrah', 7),
    (17, 'Kuldeep Yadav', 7),
    (18, 'Yuzvendra Chahal', 8),
    (19, 'Rishabh Pant', 8),
    (20, 'Sanju Samson', 8);

Now that the table is ready we can look into some examples.

Example 1: Retrieve All Subordinates of Manager with ID 3

The below query returns all subordinates of the manager with the id 3.

Query:

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 3
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT * FROM
    subordinates;

Output:

Explanation: The query starts by selecting the employee with 'employee_id = 3' (R. Sharma) as the base case. It then recursively finds all employees who report directly or indirectly to him.

Example 2: Retrieve All Subordinates of Manager with ID 4

The below query returns all subordinates of the manager with the id 4.

Query:

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 4
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT * FROM
    subordinates;

Output:

Explanation: The recursion starts with S. Raina and finds all employees reporting to him, either directly or indirectly.

Conclusion

Recursive queries in PostgreSQL are powerful tools for handling hierarchical data. By leveraging the WITH RECURSIVE statement, you can simplify complex queries and enhance your ability to query relationships within your data. Understanding the different components of recursive queries and how they work is crucial for building efficient and scalable PostgreSQL applications.