Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 41C8563386A for ; Wed, 4 Nov 2009 18:03:50 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 17870-05 for ; Wed, 4 Nov 2009 22:03:40 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f186.google.com (mail-iw0-f186.google.com [209.85.223.186]) by mail.postgresql.org (Postfix) with ESMTP id 99736633652 for ; Wed, 4 Nov 2009 18:03:35 -0400 (AST) Received: by iwn16 with SMTP id 16so5100184iwn.29 for ; Wed, 04 Nov 2009 14:03:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=WnjZHdgVfd44RUujxiRhthPaTRlFkbhTjJcVC8BBEtM=; b=atWGAkGvn+2Zcm/DQ/3t+7wHuYebVd9p9evFkZlacizAw7hGKUA2C+n+eJLTL5r5xn YLiAOcTwaRUiBfJ6ncnHlWe/wIZ+fBTqCMHojkwG0wMZkUAoByqjzQh5WBVcnjSrROdy CFgd9ONTQPWeMtgl8i8kksg/ECA5PJhrzcPjo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=IYa5ggxjtr5n6WijJfiosNKzdO93rSv7jVo3Pg0pGgyj57ll8vCE0OT3EKjlA4Uv5B UvC58i1Y0tvC4RzhKiRvHT6hY4Qc1jqx2QpcZpFOlCisaOSflRcx4SjpoH0Gc8gSuTvb OdoCKWyYXJvrh0xgzWueNdP43sZN8f7EjjflM= MIME-Version: 1.0 Received: by 10.231.125.100 with SMTP id x36mr925313ibr.52.1257372213790; Wed, 04 Nov 2009 14:03:33 -0800 (PST) Date: Wed, 4 Nov 2009 14:03:33 -0800 Message-ID: <396486430911041403u639c750bn488ada60822448c0@mail.gmail.com> Subject: Learning about WITH RECURSIVE From: Richard Broersma To: sql pgsql Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.74 tagged_above=-10 required=5 tests=BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 200911/20 X-Sequence-Number: 33371 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