The simplest recursive CTE

I found this really simple recursive CTE useful for ensuring I understood how to write recursive CTEs.

with recursive counter(x) as (
  select 0
    union
  select x + 1 from counter
)
select * from counter limit 5;

This query returns five rows from a single column x - from 0 to 4.

If you write with recursive counter as ..., omitting the (x), you get the following error:

no such column: x

You can fix that by assigning x as the alias in the first part of that union:

with recursive counter as (
  select 0 as x
    union
  select x + 1 from counter
)
select * from counter limit 5;

So that counter(x) formulation is really just a way to define the column names up front.

This query returns two columns, x and y:

with recursive counter(x, y) as (
  select 0 as x, 1 as y
    union
  select x + 1, y + 2 from counter
)
select * from counter limit 5;

Created 2022-03-20T21:32:23-07:00, updated 2022-03-25T12:23:53-07:00 · History · Edit