Postgres Pro
Downloads
Home   >   mailing lists

Thread: Performing antijoin in postgres

Performing antijoin in postgres

From
Suresh_
Date:
I want to add an antijoin operator to PostgreSql.  Basically I want to store
the result (tupletableslot) of one join and then compare it with
another..How do I store these results  and how do I compare them ?
-- 
View this message in context: http://www.nabble.com/Performing-antijoin-in-postgres-tf4358905.html#a12422596
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: Performing antijoin in postgres

From
"Heikki Linnakangas"
Date:
Suresh_ wrote:
> I want to add an antijoin operator to PostgreSql.  Basically I want to store
> the result (tupletableslot) of one join and then compare it with
> another..How do I store these results  and how do I compare them ?

I would suggest implementing it at a higher level, rewriting a query
like "A ANTIJOIN B" to "A EXCEPT (A JOIN B)". That allows the planner to
use whatever methods it has to perform the EXCEPT and the JOIN
operations. At the moment there's only one method: sort the inputs and
do a merge, but it's conceivable that you could implement EXCEPT using
hashing, for example.

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com

Re: Performing antijoin in postgres

From
Gregory Stark
Date:
"Suresh_" <suiyengar@yahoo.com> writes:

> I want to add an antijoin operator to PostgreSql.  

I think you can already do what you want like this:

SELECT * FROM A FULL OUTER JOIN B ON (x=y)WHERE x IS NULL   OR y IS NULL

So for example:

postgres=# select * from a;i 
---12
(2 rows)

postgres=# select * from b;i 
---23
(2 rows)

postgres=# select * from a full outer join b on (a.i=b.i) where a.i is null or b.i is null;i | i 
---+---1 |    | 3
(2 rows)

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

Re: Performing antijoin in postgres

From
Suresh_
Date:

Thanks for the reply. I can do this at the higher level; but this antijoin
thing is a small part of a bigger project. So I want to do it internally in
one of the joins. Does anyone have experience in storing tupleslpts and
comparing them ??



Gregory Stark wrote:
> 
> "Suresh_" <suiyengar@yahoo.com> writes:
> 
>> I want to add an antijoin operator to PostgreSql.  
> 
> I think you can already do what you want like this:
> 
> SELECT *
>   FROM A FULL OUTER JOIN B ON (x=y)
>  WHERE x IS NULL
>     OR y IS NULL
> 
> So for example:
> 
> postgres=# select * from a;
>  i 
> ---
>  1
>  2
> (2 rows)
> 
> postgres=# select * from b;
>  i 
> ---
>  2
>  3
> (2 rows)
> 
> postgres=# select * from a full outer join b on (a.i=b.i) where a.i is
> null or b.i is null;
>  i | i 
> ---+---
>  1 |  
>    | 3
> (2 rows)
> 
> -- 
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 
> 

-- 
View this message in context: http://www.nabble.com/Performing-antijoin-in-postgres-tf4358905.html#a12425655
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.