[postgis-users] Diff. Between 2 tables

[Francois Hugues]

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-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] De la part de Lee Hachadoorian
Envoyé : jeudi 7 juin 2012 16:31
À : postgis-users at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120607/80bc114c/attachment.html>