From: mark.dingee@cox.net
To: pgsql-sql@postgresql.org
Subject: Question on UNION
Date: Mon, 22 May 2006 14:58:11 -0400
Message-ID: <21062336.1148324291670.JavaMail.root@eastrmwml05.mgt.cox.net> (raw)
Everyone,
I ran into something I wasn't expecting while developing a new application. I have two similar tables that are occasionally unioned pulling only about 3 fields from each. During my testing phase I noticed that the union statement was returning what appeared to be a distinct list rather than a pure union such as is illustrated below:
create table t1 (
f1 serial primary key,
f2 text,
f3 text,
f4 integer
);
create table t2 (
f1 serial primary key,
f2 text,
f3 text,
f4 integer
);
insert into t1 (f2, f3, f4) values ('A', 'a', 1);
insert into t1 (f2, f3, f4) values ('A', 'a', 1);
insert into t1 (f2, f3, f4) values ('B', 'b', 2);
insert into t1 (f2, f3, f4) values ('B', 'b', 2);
insert into t2 (f2, f3, f4) values ('C', 'c', 3);
insert into t2 (f2, f3, f4) values ('C', 'c', 3);
insert into t2 (f2, f3, f4) values ('D', 'd', 4);
insert into t2 (f2, f3, f4) values ('D', 'd', 4);
temp=# select * from t1;
f1 | f2 | f3 | f4
----+----+----+----
1 | A | a | 1
2 | A | a | 1
3 | B | b | 2
4 | B | b | 2
(4 rows)
temp=# select * from t2;
f1 | f2 | f3 | f4
----+----+----+----
1 | C | c | 3
2 | C | c | 3
3 | D | d | 4
4 | D | d | 4
(4 rows)
When I leave out f1 I receive a distinct list:
temp=# (select f2, f3, f4 from t1) UNION (select f2, f3, f4 from t2) order by f4;
f2 | f3 | f4
----+----+----
A | a | 1
B | b | 2
C | c | 3
D | d | 4
(4 rows)
When the key field is included I get a full list:
temp=# (select * from t1) UNION (select * from t2) order by f4;
f1 | f2 | f3 | f4
----+----+----+----
1 | A | a | 1
2 | A | a | 1
3 | B | b | 2
4 | B | b | 2
1 | C | c | 3
2 | C | c | 3
3 | D | d | 4
4 | D | d | 4
(8 rows)
Can anyone explain the underlying principle(s) in the UNION that would cause this?
Thanks in advance
Mark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mark.dingee@cox.net
Subject: Re: Question on UNION
In-Reply-To: <21062336.1148324291670.JavaMail.root@eastrmwml05.mgt.cox.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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