PostgreSQL – Recursive Query Using CTEs

Strictly speaking, this process is iteration, not recursion, but RECURSIVE is the terminology chosen by the SQL standards committee.

The general structure of the Postgres  recursive query contains,

1. Non-recursive select statement
2. Union or union all
3. Recursive select statement

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 Postres recursive query work:

1. Evaluate non-recursive statements and creates a temporary table
2. Evaluate recursive terms and add them to the temporary table
3. Repeat step 2 till the working table is empty.

The difference between union and union all is that union all allows duplicates union will not allow any duplicates.

Example:

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

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

Postres recursive query to find factorial of a natural number:

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;

This query outputs two tables one with the first five natural numbers and the other table with calculations that are performed to find the factorial.

We can print only the last row but here we can see how the iteration and calculation take place.

 Postres recursive query to print Fibonacci series:

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;

This prints the Fibonacci series up to 10.

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

To create a table:

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: