ogr2ogr postgres import: check if dataset exists

Question

I have a PostGIS database which I update daily with ogr2ogr using CSV files. This works great.

Is there a way to check whether the actual value of a column already exists in the table (e.g. "event_id"=abc123 (no primary key)) and if so, that it will not append the dataset to the table. The other datasets that are not in the table should be appended to the table.

This is my actual command-line:

ogr2ogr -f "PostgreSQL" -append PG:"host=localhost user=user password=pw dbname=test" data.vrt -nln table_name

• John Powell: i don't think so via Ogr2ogr. You could insert into a temp table and then use some kind of upsert into you main table or use a trigger. Not having a primary or unique key on a column that should be unique is generally a bad idea, though it sounds like you know that.
• Basti: thank you. Solved the problem with a temp table. If you write your comment as an answer, I will tick it as solved.
• John Powell: You are welcome. I have attempted to make the answer a bit more useful than my original comment.

Answer 1

What you are looking for is essentially an upsert (albeit without the update part). This is not part of ogr2ogr and is fairly complicated to implement in Postgres, see the docs, for more information than you would ever want to know about upserts.

A simple alternative, would be to use a temp table to insert into from ogr2ogr and then run an insert for only those rows where there is no event_id, using either a left join or a not exists clause.

INSERT INTO some_table 
SELECT t.* 
FROM some_table t 
LEFT JOIN temp_table tmp ON t.event_id = tmp.event_id 
WHERE tmp.event_id IS null;