A recursive CTE allows you to perform recursion within a query using the WITH RECURSIVEsyntax. Use a recursive query to deal with hierarchical or nested data structures such as trees or graphs.

WITH RECURSIVE cte_name (column1, column2, ...)
AS(
    -- anchor member
    SELECT select_list FROM table1 WHERE condition

    UNION [ALL]

    -- recursive member
    SELECT select_list FROM cte_name WHERE recursive_condition
)
SELECT * FROM cte_name;

How PostgreSQL executes:

  1. Execute the anchor member to create the base result set (R0)
  2. Execute the recursive member with Ri as an input to return the result set Ri+1 as the output
  3. Repeat step 2 until an empty set is returned
  4. Return the final result set that is a UNION or UNION ALL of the result sets R0, R1, … Rn

Practice:

References