You could also try with not exists
select * from table2 where not exists (select unique_key from table1 where table1.unique_key = table2.unique_key) Hugues. ________________________________ De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Lee Hachadoorian Envoyé : jeudi 7 juin 2012 16:31 À : postgis-users@postgis.refractions.net Objet : Re: [postgis-users] Diff. Between 2 tables On 06/07/2012 09:14 AM, Hemin Tofiq wrote: Thank you, I have 2 tables with the same structure, so we don't need to create alias for the same table, however that is fine with updated rows, how can I get newly created rows? Regards, Hemin I assume you mean you have added a new row to one table but not the "twin" table. Comparing on a unique key would look like: SELECT * FROM table1 LEFT JOIN table2 ON (table1.unique_key = table2.unique_key) WHERE table2.unique_key IS NULL; I think you said you want to be able to do this on geometries. I haven't tried this, but I assume something like this might work: SELECT * FROM table1 LEFT JOIN table2 ON (ST_Equals(table1.the_geom, table2.the_geom)) WHERE table2.the_geom IS NULL; Just make sure to read the docs on ST_Equals vs. ST_OrderingEquals so that you know which comparison you want. http://postgis.refractions.net/documentation/manual-1.5/ST_Equals.html http://postgis.refractions.net/documentation/manual-1.5/ST_OrderingEquals.html Best, --Lee -- Lee Hachadoorian PhD, Earth & Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users