[postgis-users] Append only new features

[pcreso at pcreso.com]

brizey02 wrote:
> I am trying to load the combined
> http://www.weather.gov/regsci/gis/shapefiles/allwarn.zip but
> I only want to load the new warnings is there a way to load only the
> new geometries? I was doing a append but there were warnings being
> duplicated until they expired,
>
> I know there are 3 unique identifiers WFO, TYPE,ETN is
> there a way to load only the new ones?

option 1:

Create a unique index (or primary key) on the target table and load the records using separate inserts for each record, NOT combined as a single transaction.

Any inserts of a duplicate will fail due to the unique constraint, the others should load fine. 


option 2:

load the entire shapefile into a temporary table, then insert those records which do not already exist into the target table. One possible way is:

insert into table
select * from temp_table
where wfo||'_'||type||'_'||etn
not in (select distinct wfo||'_'||type||'_'||etn from table);


HTH,

   Brent Wood