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
