pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLearning about WITH RECURSIVE
5+ messages / 2 participants
[nested] [flat]
* Learning about WITH RECURSIVE
@ 2009-11-04 22:03 Richard Broersma <richard.broersma@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Broersma @ 2009-11-04 22:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Learning about WITH RECURSIVE
@ 2009-11-04 22:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2009-11-04 22:11 UTC (permalink / raw)
To: Richard Broersma <richard.broersma@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Learning about WITH RECURSIVE
@ 2009-11-04 22:17 Richard Broersma <richard.broersma@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Broersma @ 2009-11-04 22:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Learning about WITH RECURSIVE
@ 2009-11-04 22:27 Richard Broersma <richard.broersma@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Broersma @ 2009-11-04 22:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Learning about WITH RECURSIVE
@ 2009-11-05 00:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2009-11-05 00:04 UTC (permalink / raw)
To: Richard Broersma <richard.broersma@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-11-05 00:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-04 22:03 Learning about WITH RECURSIVE Richard Broersma <richard.broersma@gmail.com>
2009-11-04 22:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-11-04 22:17 ` Richard Broersma <richard.broersma@gmail.com>
2009-11-04 22:27 ` Richard Broersma <richard.broersma@gmail.com>
2009-11-05 00:04 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox