How to Use Recursive CTE in PostgreSQL: Syntax and Example

In PostgreSQL, a recursive CTE is created using the WITH RECURSIVE clause to perform queries that refer to themselves. It consists of an initial query (anchor) and a recursive query that repeatedly references the CTE until no new rows are produced.

Syntax

A recursive CTE in PostgreSQL uses the WITH RECURSIVE clause followed by a CTE name and column list. It has two parts separated by UNION ALL: the anchor member (base query) and the recursive member (query that references the CTE itself). The recursion stops when the recursive member returns no new rows.

• WITH RECURSIVE cte_name (columns): Declares the recursive CTE.
• Anchor member: The initial query that seeds the recursion.
• UNION ALL: Combines anchor and recursive results.
• Recursive member: Query that references the CTE to add more rows.
• Final SELECT: Retrieves results from the CTE.

sql

WITH RECURSIVE cte_name (column1, column2, ...) AS (
  -- Anchor member: initial query
  SELECT initial_values
  UNION ALL
  -- Recursive member: references cte_name
  SELECT next_values FROM cte_name WHERE condition
)
SELECT * FROM cte_name;

Example

This example shows how to generate a sequence of numbers from 1 to 5 using a recursive CTE. The anchor member starts with 1, and the recursive member adds 1 to the previous number until it reaches 5.

sql

WITH RECURSIVE numbers(n) AS (
  SELECT 1  -- Anchor member: start at 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5  -- Recursive member: add 1 until 5
)
SELECT * FROM numbers;

Output

 n 
---
 1
 2
 3
 4
 5
(5 rows)

Common Pitfalls

Common mistakes when using recursive CTEs include:

• Forgetting RECURSIVE keyword after WITH, which makes the query fail.
• Not having a proper stopping condition in the recursive member, causing infinite loops.
• Using UNION instead of UNION ALL, which removes duplicates and can cause unexpected results or performance issues.
• Incorrectly referencing the CTE inside the recursive member.

sql

/* Wrong: Missing RECURSIVE keyword */
WITH numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 3
)
SELECT * FROM numbers;

/* Right: Include RECURSIVE keyword */
WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 3
)
SELECT * FROM numbers;

Quick Reference

• Use WITH RECURSIVE to start a recursive CTE.
• Anchor member runs once to seed results.
• Recursive member runs repeatedly, referencing the CTE.
• Use UNION ALL to combine results efficiently.
• Always include a stopping condition to avoid infinite loops.

Key Takeaways

Start recursive queries with the WITH RECURSIVE clause followed by anchor and recursive members.

Always include a stopping condition in the recursive member to prevent infinite loops.

Use UNION ALL to combine anchor and recursive results without removing duplicates.

The recursive member must reference the CTE name to build upon previous results.

Test recursive CTEs with small data sets to ensure correct behavior before scaling.