Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test1 (num int, col int);
create table test2 (num int, col int);
insert into test2 values (1,1), (2,2)
2 rows affected

insert into test1 values (1,0)
1 rows affected

insert into test1 (num, col)
select * 
from test2 
where col < 9
on conflict (num) do
update set col = excluded.col
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification


select * from test1


num
col


1
0


do $$
begin 
  update test1 t1 
  set col = t2.col
  from test2 t2 
  where t2.num = t1.num;
  
  insert into test1 (num, col)
  select num, col
  from test2 t2
  where not exists (
    select 1
    from test1 t1
    where t1.num = t2.num
  );
end $$;
select * from test1


num
col


1
1

2
2