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

Re: Wrong output from union

From: David Johnston <polobo(at)yahoo(dot)com>
To: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Wrong output from union
Date: 2012-03-31 01:19:50
Message-ID: 2F810199-0078-400F-AF95-F04F6BFAB4B0@yahoo.com (view raw or flat)
Thread:
Lists: 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(dot)stainburn(at)ringways(dot)co(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

In response to

Responses

pgsql-sql by date

Next:From: Gary StainburnDate: 2012-04-02 09:11:31
Subject: Re: Wrong output from union
Previous:From: Gary StainburnDate: 2012-03-28 14:01:38
Subject: Wrong output from union

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