[SQL] Question on UNION

[mark.dingee]

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


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Am I crazy or is this SQL not possible

[mark.dingee]

Collin,

I have a similar circumstance in one of my own apps.  I operate under
the simple presumption that the unique_id is sequential and thus the
record with the highest unique_id is the most recent entry.  In that
case I use a query such as 

select * 
from broadcast_history
where unique_id in (
select broadcast_id, max(unique_id) 
from broadcast_history
group by broadcast_id)

which permits me to examine the entire record which is necessary in my
situation.

Good luck
Mark

On Thu, 2006-06-01 at 10:43 -0700, Collin Peters wrote:
> I am having some serious mental block here.  Here is the abstract
> version of my problem.  I have a table like this:
> 
> unique_id (PK)   broadcast_id   date_sent  status
> 1  1 2005-04-0430
> 2  1 2005-04-01 30
> 3  1 2005-05-20 10
> 4  2 2005-05-29 30
> 
> So it is a table that stores broadcasts including the broadcast_id,
> the date sent, and the status of the broadcast.
> 
> What I would like to do is simply get the last date_sent and it's
> status for every broadcast.  I can't do a GROUP BY because I can't put
> an aggregate on the status column.
> 
> SELECT MAX(date_sent), status
> FROM broadcast_history
> GROUP BY broadcast_id
> 
> How do I get the status for the most recent date_sent using GROUP BY?
> 
> DISTINCT also doesn't work
> 
> SELECT DISTINCT ON (email_broadcast_id) *
> FROM email_broadcast_history
> ORDER BY date_sent
> 
> As you have to have the DISTINCT fields matching the ORDER BY fields.
> I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent
> 
> I keep thinking am I missing something.  Does anybody have any ideas?
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[SQL] Temp tables and functions

[mark.dingee]

Everyone,

I've written a function (language = sql) which uses a temporary table to 
simplify the process; however, when I go to load the function I get:

/var/lib/pgsql$cat thm.sql | psql test
ERROR:  relation "lost_bus" does not exist

where "lost_bus" is the name of my temp table.  The function is just for a 
report that is run monthly, so I can create a permanent table if necessary, but 
I'd rather not. 

Thanks in advance
Mark

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match