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;
counter(x) formulation is really just a way to define the column names up front.
This query returns two columns,
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