|

»

PostgreSQL - sql

Learning about WITH RECURSIVE

View: New views

5 Messages — Rating Filter: Alert me

	Learning about WITH RECURSIVE by Richard Broersma :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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 -- Sent via pgsql-sql mailing list (pgsql-sql@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
	Re: Learning about WITH RECURSIVE by Tom Lane-2 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message Richard Broersma <richard.broersma@...> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
	Re: Learning about WITH RECURSIVE by Richard Broersma :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane <tgl@...> wrote: > Richard Broersma <richard.broersma@...> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
	Re: Learning about WITH RECURSIVE by Richard Broersma :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane <tgl@...> wrote: > Richard Broersma <richard.broersma@...> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
	Re: Learning about WITH RECURSIVE by Tom Lane-2 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message Richard Broersma <richard.broersma@...> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql