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.stainb...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
