PostgreSQL - Recursive Query Using CTEs

Recursive queries are a powerful feature in PostgreSQL that allow you to perform iterative operations within a database. While strictly speaking, this process is iteration, the SQL standards committee chose the term RECURSIVE. This article will provide an in-depth understanding of PostgreSQL recursive queries, their syntax, examples, and best practices to ensure your queries are efficient and effective.

General Structure of a Recursive Query

The general structure of the PostgreSQL recursive query contains,

1. Non-recursive SELECT statement
2. UNION or UNION ALL
3. Recursive SELECT statement

Syntax

WITH RECURSIVE name_cte AS (
SELECT statement  /* non-recursive statement */
UNION [ALL]
SELECT statement  /*recursive statement referencing the above select statement */
)
SELECT * FROM name_cte;

How Does a PostgreSQL Recursive Query Work?

1. Evaluate Non-Recursive Statements: The initial SELECT statement is evaluated, creating a temporary table.
2. Evaluate Recursive Terms: The recursive SELECT statement is evaluated and its results are added to the temporary table.
3. Repeat: Step 2 is repeated until no more rows can be added to the temporary table.

Union vs. Union All

• UNION: Eliminates duplicate rows.
• UNION ALL: Allows duplicates.

PostgreSQL Recursive Query Using CTEs Example

Let us take a look at some of the examples of Recursive Query Using CTEs in PostgreSQL to better understand the concept.

Example 1: Generating a Sequence of Numbers

This is the basic example of PostgreSQL recursive query which prints the first 10 natural numbers.

Query:

WITH RECURSIVE tens AS (
   SELECT 1 as n
 UNION ALL
   SELECT n+1 FROM tens
)
SELECT n FROM tens limit 10;

Explanation: This query generates the first 10 natural numbers.

Example 2: Calculating Factorials

PostgreSQL recursive query to find factorial of a natural number.

Query:

WITH RECURSIVE fact (n, factorial)
AS (
    SELECT 1 as n, 5 as factorial
union all
    SELECT n+1, factorial*n FROM fact WHERE n < 5
)
SELECT * FROM fact;

Explanation: This query calculates the factorial of numbers up to 5, showing each step of the calculation.

Example 3: Generating Fibonacci Series

PostgreSQL recursive query to print Fibonacci series.

Query:

WITH RECURSIVE fibb
AS (
    SELECT 1::bigint as n, 0::bigint as a, 1::bigint as b
UNION ALL
    SELECT n+1, b as a, (a+b) as b FROM fibb
)
SELECT b FROM fibb limit 10;

Explanation: This query generates the first 10 numbers in the Fibonacci series.

Example 4: Finding Organizational Hierarchy

With the help of PostgreSQL recursive query, we can find the organizational hierarchy:

-- Creating the employees table and inserting data
CREATE TABLE employees (     
employee_id SERIAL PRIMARY KEY,     
full_name VARCHAR(100),     
manager_id INTEGER );
INSERT INTO employees (
employee_id,
full_name,
manager_id
)
VALUES
(1, 'Abhi', NULL),
(2, 'Bhargav', 1),
(3, 'Chay', 1),
(4, 'Dravid', 1),
(5, 'Erin', 1),
(6, 'Ford', 2),
(7, 'Gagan', 2),
(8, 'Harry', 3),
(9, 'Isaac', 3),
(10, 'Jack', 4),
(11, 'Kiran', 5);

Abhi is the boss, he will be on the first level. Bhargav, Chay, Dravid, Erin are in the next level and the rest of them will be the last level.

Query:

WITH RECURSIVE subordinates AS (
SELECT employee_id, manager_id, full_name, 0 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.full_name, level+1
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)  
SELECT * FROM subordinates;

The output will be:

Explanation: This query retrieves the hierarchical structure of employees, starting from the top-level manager (Abhi) and including all subordinates at each level.

Best Practices for Using PostgreSQL Recursive Queries

• If duplicates are not an issue, use UNION ALL instead of UNION to improve performance.
• Use a WHERE clause to limit the depth of recursion, preventing potential infinite loops.
• Ensure relevant columns are indexed to speed up join operations in recursive queries.
• Use descriptive names for common table expressions (CTEs) to enhance readability.
• Regularly monitor query performance, especially for large datasets.