Обсуждение: Wrong output from union
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-2825:2012-03-2825:2012-03-2825:2012-03-3025:2012-03-2925: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-2725:2012-03-2825:2012-03-2925:2012-03-30 (4 rows) users=# -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
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
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
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