Re: Check for duplicates before inserting new rows

On Fri, 4 Sep 2020, Olivier Gautherot wrote:

> First of all, what version of PostgreSQL are you using?

Olivier,

12.2.

> One way would be to add a UNIQUE constraint and perform for each row of the
> source table a INSERT ... ON CONFLICT DO NOTHING (see
> https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT)
>
> If it is a 2-way merge, I would encapsulate the process in a function
> (which will create a transaction to protect your process) and add a column
> to trace the rows that have been merged. For this purpose, you can use the
> ON CONFLICT DO UPDATE ...

Lots for me to ponder.

The new data has attributes stored in two database tables: location and
measurements. I'll need to think about how both could be handled in a single
function. For example, the new data contains a row with a location not
already in the existing location table. That location needs to be added to
the location table and its associated measurement attributes then added to
the existing measurements table.

Thanks for the suggestion,

Rich