1

I am migrating a huge chunk of PostgreSQL script to work in Snowflake. The problem that stopped me is on conflict syntax and specifically when you use on conflict do nothing.

insert into table1
  select 
    user_id
  , something_else
from table2
on conflict do nothing;

Some propose the "equivalent" of on conflict from Postgres is using merge into, some are not happy about it. However when you use merge into you have to specify an on <CONDITION> clause, e.g. merge into t1 using t2 on t1.id = t2.id ....

But in case of on conflict do nothing what should be the alternative?

When using merge into is there a less verbose syntax than specifying every column in these cases? (Imagine you have 15 columns and you have to write every one of them).

atoth
  • 838
  • 1
  • 9
  • 23

1 Answers1

2

Try this (assumes you want to use user_id for uniqueness criteria)

merge into table1
using (select user_id, something_else from table2) sub
on sub.user_id = table1.user_id
when not matched then insert values(sub.user_id, sub.something_else)

Or simply eliminate the rows that have an existing user_id

insert into table1
select user_id, something_else from table2
where user_id not in (select user_id from table1)

Or if you want to prevent only full row duplicates, do 

insert into table1 ( 
  select user_id, something_else from table2
  minus
  select * from table1
)
Marcin Zukowski
  • 4,281
  • 1
  • 19
  • 28
  • The last one is the closest I think, since I have checked and not all of the tables have unique ids. I will accept when I confirm it worked out! Thanks! – atoth Feb 25 '19 at 10:55