Обсуждение: Wrong output from union

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-2825:2012-03-2825:2012-03-2825:2012-03-3025:2012-03-2925: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-2725:2012-03-2825:2012-03-2925:2012-03-30
(4 rows)

users=#
-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.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

Thanks for this David.

Does anyone know why it too until Saturday for my post to appear on the listr?

Gary


On Saturday 31 March 2012 02:19:50 David Johnston wrote:
> 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
>


-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk 

On 2012-04-02, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> Thanks for this David.
>
> Does anyone know why it too until Saturday for my post to appear on the listr?
>
> Gary

If you haven't signed up for the mailing list with exactly the same email 
address that you post from the message will be held for moderation.

(standard weak antispam procedure)

-- 
⚂⚃ 100% natural