How do you use a recursive CTE to generate a date series or number sequence when your database lacks a built-in generate_series function?

How to think about it

The real subject here is why date spines exist: without one, dates with no activity simply vanish from your result set, breaking line charts and period-over-period math. A recursive CTE manufactures every date in a range so a LEFT JOIN can keep the empty days visible.

Every sequence-generating recursive CTE has the same three parts:

1. Anchor — seed the first value.
2. Recursive member — increment by one step and UNION ALL it back.
3. Termination — a WHERE that stops the loop.

WITH RECURSIVE nums AS (
  SELECT 1 AS n                    -- anchor
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10   -- recursive + termination
)
SELECT n FROM nums;                -- 1..10

Swap integer arithmetic for date arithmetic and the same skeleton builds a date spine.

A worked example — fill the gaps in a fact table

The spine is the left side of a LEFT JOIN, so every day appears even when sales didn’t happen:

WITH RECURSIVE date_spine AS (
  SELECT DATE('2024-01-01') AS dt
  UNION ALL
  SELECT DATE(dt, '+1 day') FROM date_spine WHERE dt < DATE('2024-01-07')
),
daily AS (
  SELECT sale_date, SUM(amount) AS revenue FROM sales GROUP BY sale_date
)
SELECT s.dt AS day,
       COALESCE(d.revenue, 0) AS revenue   -- 0 instead of a missing row
FROM date_spine s
LEFT JOIN daily d ON s.dt = d.sale_date
ORDER BY s.dt;

day	revenue
2024-01-01	200
2024-01-02	200
2024-01-03	150
2024-01-04	0
2024-01-05	90
2024-01-06	310
2024-01-07	0

Jan 4 and Jan 7 had no sales, yet they appear as 0 rather than dropping out — that’s the whole point of the spine. Without it, a chart would silently jump from Jan 3 straight to Jan 5 and misrepresent the trend.

Why the recursion terminates

Each pass of the recursive member sees only the rows from the previous pass, not the whole accumulated set. Pass 1 holds 2024-01-01; the engine computes DATE(dt, '+1 day') → 2024-01-02, appends it, and repeats until dt reaches the WHERE bound. No stored table is touched — it’s pure computation that halts because the seed marches toward the stop condition.