Skip site navigation (1) Skip section navigation (2)

Wrong output from union

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Wrong output from union
Date: 2012-03-28 14:01:38
Message-ID: 201203281501.38835.gary.stainburn@ringways.co.uk (view raw or flat)
Thread:
Lists: pgsql-sql
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 

Responses

pgsql-sql by date

Next:From: David JohnstonDate: 2012-03-31 01:19:50
Subject: Re: Wrong output from union
Previous:From: Steve CrawfordDate: 2012-03-27 16:12:47
Subject: Re: how to concatenate in PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group