Postgres Pro
Downloads
Home   >   mailing lists

Thread: Learning about WITH RECURSIVE

Learning about WITH RECURSIVE

From
Richard Broersma
Date:
Can anyone one explain why a "WITH RECURSIVE" query has the same
results regardless whether UNION or UNION ALL is specified?

broersr=> WITH RECURSIVE t(n) AS (
broersr(>     VALUES (1)
broersr(>   UNION
broersr(>     SELECT n+1 FROM t WHERE n < 100
broersr(> )
broersr-> SELECT sum(n) FROM t;sum
------5050
(1 row)

broersr=> WITH RECURSIVE t(n) AS (
broersr(>     VALUES (1)
broersr(>   UNION ALL
broersr(>     SELECT n+1 FROM t WHERE n < 100
broersr(> )
broersr-> SELECT sum(n) FROM t;sum
------5050
(1 row)

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: Learning about WITH RECURSIVE

From
Tom Lane
Date:
Richard Broersma <richard.broersma@gmail.com> writes:
> Can anyone one explain why a "WITH RECURSIVE" query has the same
> results regardless whether UNION or UNION ALL is specified?

Well, if the rows are all different anyway, UNION isn't going to
eliminate any ...
        regards, tom lane

Re: Learning about WITH RECURSIVE

From
Richard Broersma
Date:
On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Broersma <richard.broersma@gmail.com> writes:
>> Can anyone one explain why a "WITH RECURSIVE" query has the same
>> results regardless whether UNION or UNION ALL is specified?
>
> Well, if the rows are all different anyway, UNION isn't going to
> eliminate any ...

Okay thanks.  I see that I need to spend more time with the on-line docs.

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: Learning about WITH RECURSIVE

From
Richard Broersma
Date:
On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Broersma <richard.broersma@gmail.com> writes:
>> Can anyone one explain why a "WITH RECURSIVE" query has the same
>> results regardless whether UNION or UNION ALL is specified?
>
> Well, if the rows are all different anyway, UNION isn't going to
> eliminate any ...


Actually I'm still confused.  I must me missing something.  When I
manually following the directions of:
http://www.postgresql.org/docs/8.4/interactive/queries-with.html

I get the following when I try:

WITH RECURSIVE t(n) AS (   VALUES (1) UNION ALL   SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

(1) --initial non-recursive working table

(1) UA (2) = (1,2) --new(1) working table

(1,2) UA (2,3) = (1,2,2,3) --new(2) working table

(1,2,2,3) UA (2,3,3,4) = (1,2,2,2,3,3,3,4) --new(3) working table




-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: Learning about WITH RECURSIVE

From
Tom Lane
Date:
Richard Broersma <richard.broersma@gmail.com> writes:
> Actually I'm still confused.  I must me missing something.  When I
> manually following the directions of:
> http://www.postgresql.org/docs/8.4/interactive/queries-with.html

> I get the following when I try:

> WITH RECURSIVE t(n) AS (
>     VALUES (1)
>   UNION ALL
>     SELECT n+1 FROM t WHERE n < 100
> )
> SELECT sum(n) FROM t;

> (1) --initial non-recursive working table

> (1) UA (2) = (1,2) --new(1) working table

> (1,2) UA (2,3) = (1,2,2,3) --new(2) working table

> (1,2,2,3) UA (2,3,3,4) = (1,2,2,2,3,3,3,4) --new(3) working table

You're confusing the working table with the final output.  In this
test case, the working table contains exactly one row after each
step (except after the last, when it contains no rows).  That one
row is also added to the result, but we don't use the whole result
for the next iteration of the recursive term.
        regards, tom lane