How to protect the target table from duplicates when inserting data from a temporary table? How to update existing records with more recent data (upsert)?

To protect the target table from duplicates when inserting data from a temporary table and simultaneously update existing records with more recent data (upsert operation), the following approaches can be used:

1. Unique constraints and index

   • The target table should have a unique index or a uniqueness constraint on key fields that define duplicates.

2. Using the UPSERT construct

   • Modern DBMSs (e.g., PostgreSQL, MySQL) support the INSERT ... ON CONFLICT ... DO UPDATE or INSERT ... ON DUPLICATE KEY UPDATE construct.

Example for PostgreSQL:

INSERT INTO target_table (id, data, updated_at)
SELECT id, data, updated_at FROM temp_table
ON CONFLICT (id) DO UPDATE SET
    data = EXCLUDED.data,
    updated_at = EXCLUDED.updated_at
WHERE target_table.updated_at < EXCLUDED.updated_at;

Here:

• ON CONFLICT (id) — conflict on the unique key id.
• In the DO UPDATE block, update fields if the data from the temporary table is more recent (e.g., based on the updated_at field).

3. Alternative approach — MERGE (if supported)

MERGE INTO target_table t
USING temp_table s
ON t.id = s.id
WHEN MATCHED AND t.updated_at < s.updated_at THEN
  UPDATE SET data = s.data, updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (id, data, updated_at) VALUES (s.id, s.data, s.updated_at);

4. Preliminary removal of duplicates in the temporary table

If the temporary table may contain duplicates, it is advisable to eliminate them first, for example, using DISTINCT or aggregate functions.

Thus, combining unique constraints and UPSERT operations ensures protection against duplicates and updates existing records with more recent data.