[SQL] find the "missing" rows

[Kevin B.]

I would like to find the "missing" rows between two sets without using a
subselect (or views).

This query finds the rows that are in t1 but not in t2. (see the script
below for table definitions.)

QUERY 1:

select * from t1 left join t2 on t1.i = t2.i where t2.i is null

The above query is across two tables.  I'm having trouble when all the
data is in the same table. Here is my attempt but it does not work.  I've
thought about doing it with views but I really would like to try without
views or a subselect...

QUERY 2:

Select a.i, b.i
from t as a
left join t as b on a.i = b.i
where a.n = 'a' and b.n = 'b' and b.i is null

Is there some clever trick get a query working in similar fashion to QUERY
1 but when all the data is in the same table (as in table "t")?

Definitions for Query 1

create table t1 (i int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(4);
insert into t1 values(5);

create table t2 (i int);
insert into t2 values(1);
insert into t2 values(2);
insert into t2 values(3);
insert into t2 values(5);


Definitions for Query 2

create table t (n varchar(10), i int);
insert into t values('a',1);
insert into t values('a',2);
insert into t values('a',3);
insert into t values('a',4);
insert into t values('a',5);
insert into t values('b',1);
insert into t values('b',2);
insert into t values('b',3);
insert into t values('b',5);












---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] filtering

[Kevin B.]

Hi,

I have a 14 million row table with one index on two fields one is a varchar
the other is a date.  The combination of the two makes the row unique.

Data
-
name  date... other fields
a 1/1/01
a 1/2/01
a 1/3/01
b 1/1/01
b 1/2/01
d 1/1/01
d 1/2/01

I have a table with just the names.  each name occurs once.
UName
-
name
a
b
c
d

I've tried a number of queries to find which name is in UName but not in
Data.   However, they are all taking too long (more than 30 minutes - but
the hard drive is a slow 4200rpm IDE).

What is the quickest query to get the result that I want?  Also, should I
put another index on the Data table for "name" only?

Thanks









---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] assign the row count of a query to a variable

[Kevin B.]

Hi,

I'm trying to assign the row count of a query to a variable in a function
but I'm not having any luck.

Could someone tell me the syntax? I've been looking in the docs and
googling for a long time but just can't find the answer.

I've tried:
CREATE OR REPLACE FUNCTION ret1() RETURNS int4 AS '
BEGIN
declare
var int4;
begin
  --select var count(*) from T;
  --select var (count(*)) from T;
  --select var = count(*) from T;
  var = select count(*) from T;
   return   var;
END;
END;
'
LANGUAGE 'plpgsql';


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html