Re: [HACKERS] Performing antijoin in postgres
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Performing antijoin in postgres
Suresh_ [EMAIL PROTECTED] 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
Re: [HACKERS] Performing antijoin in postgres
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_ [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate