From: David Johnston <polobo@yahoo.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Wrong output from union
Date: Fri, 30 Mar 2012 18:19:50 -0700
Message-ID: <2F810199-0078-400F-AF95-F04F6BFAB4B0@yahoo.com> (raw)
In-Reply-To: <201203281501.38835.gary.stainburn@ringways.co.uk>
References: <201203281501.38835.gary.stainburn@ringways.co.uk>
Documented behavior. Please read the section on UNION for the why and the proper alternative syntax:
http://www.postgresql.org/docs/9.0/interactive/sql-select.html
On Mar 28, 2012, at 7:01, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> Hi folks.
>
> I have two selects which in themselves report what they should. However, when
> I run a union to combine their outputs (to then feed a select/count) lines
> disappear. Below are the two seperate selects, followed by the untion in
> which duplicate rows are lost. I'm using Postgresql 8.3.3-2 RPMs on a Fedora
> 9 system.
>
> Can anyone tell me why it is happening and how to fix it.
>
> Ta.
>
> users=# select r_u_id || ':' ||r_deadline as key from requests where r_u_id is
> not NULL and r_deadline>='2012-03-26' and r_deadline <= ('2012-03-26'::date
> + '7 days'::interval);
> key
> ---------------
> 25:2012-03-28
> 25:2012-03-28
> 25:2012-03-28
> 25:2012-03-30
> 25:2012-03-29
> 25:2012-03-27
> (6 rows)
>
> users=# select u_id || ':' || rm_timestamp::date as key from request_reminders
> where u_id is not null and rm_timestamp>='2012-03-26' and rm_timestamp <=
> ('2012-03-26'::date + '7 days'::interval);
> key
> -----
> (0 rows)
>
> users=# select r_u_id || ':' ||r_deadline as key from requests where r_u_id is
> not NULL and r_deadline>='2012-03-26' and r_deadline <= ('2012-03-26'::date
> + '7 days'::interval)
> union
> select u_id || ':' || rm_timestamp::date as key from request_reminders where
> u_id is not null and rm_timestamp>='2012-03-26' and rm_timestamp <=
> ('2012-03-26'::date + '7 days'::interval);
> key
> ---------------
> 25:2012-03-27
> 25:2012-03-28
> 25:2012-03-29
> 25:2012-03-30
> (4 rows)
>
> users=#
> --
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, gary.stainburn@ringways.co.uk
Subject: Re: Wrong output from union
In-Reply-To: <2F810199-0078-400F-AF95-F04F6BFAB4B0@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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