In Amazon Redshift, how can I bulk insert rows only if they don't already exist?

Question

In Redshift, I am doing a bulk insert of values into a table. However I only want to insert the values in this list that do not already exist in the table, to avoid adding dupes.

INSERT INTO $TEST_TABLE values (A, 1), (B, 2), (C, 3)

If table already contains(A, 1), I want the statement to only insert (B, 2) and (C, 3).

Thanks!

Answer 1

There is no automatic way to only insert only non-existent rows (often called UPSERT = Update existing rows, insert new rows).

You could load the data into a temporary table, then do a command like this to only insert rows that aren't there:

INSERT INTO target
SELECT * FROM temp
WHERE temp.primary_key NOT IN (SELECT DISTINCT primary_key FROM target)

If desired, a similar method can be used to also Update values where they already exist.