The simplest recursive CTE#

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

1
with recursive counter(x) as (
2
  select 0
3
    union
4
  select x + 1 from counter
5
)
6
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:

1
with recursive counter as (
2
  select 0 as x
3
    union
4
  select x + 1 from counter
5
)
6
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:

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