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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-03-31 01:19:50 | Re: Wrong output from union |
Previous Message | Steve Crawford | 2012-03-27 16:12:47 | Re: how to concatenate in PostgreSQL |