pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWrong output from union
4+ messages / 3 participants
[nested] [flat]
* Wrong output from union
@ 2012-03-28 14:01 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Gary Stainburn @ 2012-03-28 14:01 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Wrong output from union
@ 2012-03-31 01:19 David Johnston <polobo@yahoo.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: David Johnston @ 2012-03-31 01:19 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Wrong output from union
@ 2012-04-02 09:11 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Gary Stainburn @ 2012-04-02 09:11 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Wrong output from union
@ 2012-04-03 10:49 Jasen Betts <jasen@xnet.co.nz>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Jasen Betts @ 2012-04-03 10:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-04-03 10:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-28 14:01 Wrong output from union Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-03-31 01:19 ` David Johnston <polobo@yahoo.com>
2012-04-02 09:11 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-04-03 10:49 ` Jasen Betts <jasen@xnet.co.nz>
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