Recursive Query

PostgreSQL Query: Select Recursive Query
Categories:
Tags:

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

First 10 natural numbers

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.

recursive query

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.

recursive query

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.

recursive query

Organizational hierarchy

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.

recursive query

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:

recursive query

Reference