Issue with recursive CTE in PostgreSQL

Question

This query generates the numbers from 1 to 4.

with recursive z(q) as (
  select 1
  union all
  select q + 1 from z where q < 4
  )
select * from z;

But, if I modify it to this,

with x as (
  select 1 y
  ),
recursive z(q) as (
  select y from x
  union all
  select q + 1 from z where q < 4
  )
select * from z;

It gives

ERROR: syntax error at or near "z"

What did i do wrong here?

Later related answer with more details: stackoverflow.com/a/35249370/939860 — Erwin Brandstetter
– Erwin Brandstetter, Commented May 17, 2017 at 16:57

roman · Accepted Answer · 2013-10-17 07:22:05Z

4

I think this is because RECURSIVE is modifier of WITH statement, not a property of common table expression z, so you can use it like this:

with recursive
x as (
  select 1 y
),
z(q) as (
  select y from x
  union all
  select q + 1 from z where q < 4
)
select * from z;

answered Oct 17, 2013 at 7:22

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Recursive enables self-referencing queries in any of the list of multiple CTEs, not just the first.