Generate Series Using Recursive CTE in PostgreSQL: Syntax & Example

In PostgreSQL, you can generate a series using a recursive CTE by defining an initial value and recursively selecting the next value until a condition is met. The CTE repeatedly unions the current value with the next incremented value until the series completes.

Syntax

A recursive CTE in PostgreSQL has two parts: the anchor member and the recursive member. The anchor member defines the starting point of the series, and the recursive member references the CTE itself to generate the next values. The recursion stops when the condition in the WHERE clause is no longer true.

• WITH RECURSIVE cte_name AS: Starts the recursive CTE.
• Anchor member: Initial SELECT statement with the first value.
• UNION ALL: Combines anchor and recursive parts.
• Recursive member: SELECT that references the CTE to generate next values.
• Termination condition: Limits recursion to stop at desired value.

sql

WITH RECURSIVE series AS (
  SELECT start_value AS num  -- anchor member
  UNION ALL
  SELECT num + 1 FROM series WHERE num < end_value  -- recursive member
)
SELECT num FROM series;

Example

This example generates a series of numbers from 1 to 5 using a recursive CTE. It starts at 1 and adds 1 each time until it reaches 5.

sql

WITH RECURSIVE series AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM series WHERE num < 5
)
SELECT num FROM series;

Output

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

Common Pitfalls

Common mistakes when using recursive CTEs for series generation include:

• Forgetting the termination condition, which causes infinite recursion and query failure.
• Using UNION instead of UNION ALL, which can slow down the query due to duplicate elimination.
• Incorrectly referencing the CTE name inside the recursive member.

sql

/* Wrong: Missing termination condition causes infinite recursion */
WITH RECURSIVE series AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM series
)
SELECT num FROM series;

/* Correct: Add termination condition to stop recursion */
WITH RECURSIVE series AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM series WHERE num < 5
)
SELECT num FROM series;

Quick Reference

Part	Description	Example
WITH RECURSIVE	Starts the recursive CTE	WITH RECURSIVE series AS (...)
Anchor member	Initial row(s) to start the series	SELECT 1 AS num
UNION ALL	Combine anchor and recursive parts without removing duplicates	UNION ALL
Recursive member	Generates next rows by referencing CTE	SELECT num + 1 FROM series WHERE num < 5
Termination condition	Stops recursion to prevent infinite loop	WHERE num < 5

Key Takeaways

Use a recursive CTE with an anchor and recursive member to generate series in PostgreSQL.

Always include a termination condition to avoid infinite recursion.

Use UNION ALL instead of UNION for better performance in recursive queries.

The recursive member must reference the CTE name to generate the next values.

Recursive CTEs are flexible for generating custom sequences beyond built-in functions.