Insert into one table from another Postgres based on two columns

Question

I have two tables with the same columns, There are no unique columns for these tables. Lets say the columns are Col1, Col2, Col3 and Col4. The tables are T1 and T2.

What I want to do is insert all the rows from T2 to T1 where Col1 & Col2 combinations do not exist in T1 already. Col1 is a string and Col2 is an int.

So for example Col1 = "APPLE" and Col2 = "2019". If a row contains Col1 = "APPLE" and Col2=2019 in T2 I do not want to insert it into T1 whereas if a row contains Col1 = "APPLE" and Col2=2020 then I want to insert it into T1.

I am trying to find the easiest solution to do this and cant seem to find a straightforward way using INSERT INTO WHERE NOT EXISTS or using UPSERT.

Answer 1

You can use insert ... select ... where not exists with tuple equality to compare (col1, col2):

insert into t1(col1, col2, col3, col4)
select * from t2
where not exists (
    select 1 from t1 where (t1.col1, t1.col2) = (t2.col1, t2.col2)
)

Answer 2

With NOT EXISTS:

insert into t1(Col1, Col2, Col3, Col4)
select Col1, Col2, Col3, Col4
from t2
where not exists (
  select 1 from t1
  where t1.Col1  = t2.Col1 and t1.Col2  = t2.Col2
)

See a simplified demo.