Viswamitra's blog

Merge also called as upsert is introduced in SQL:2003. This statement is mainly used to compare a table or view(updatable) with some data source(SQL or table) based on some condition. If the condition matches we can perform the update to the target table. If the condition is not matched then we can insert the data into the target table.

Syntax as per SQL standard 2003 :-

MERGE INTO tablename USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 …]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 …]) VALUES (value1 [, value2 …]);
 

As of postgres 9.5 there is no merge statment may be we will get a full version of merge in future release ,but in 9.5 we have “upsert” feature implemented using the below statement.

INSERT … ON CONFLICT DO NOTHING/UPDATE (“UPSERT”)

This clause specifies an alternative action to take in the event of a would-be duplicate violation.

INSERT INTO countries (country) VALUES (‘France’),(‘Japan’) ON CONFLICT (country) DO NOTHING;

NOTE : There should be a unique constraint on column used in ON CONFLICT clause.

INSERT INTO user_logins (username, logins)
 VALUES (‘Naomi’,1),(‘James’,1)
 ON CONFLICT (username)
 DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

NOTE :-

This is not a full fledged merge like other databases

In postgres :-

postgres=# insert into target (id,name) (select id,name from source) on conflict
 (id,name) DO NOTHING;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT spec
ification

In postgres if we need to have a unique constraint on the columns we are using in “ON CONFLICT” clause.

In Oracle :-

SQL> merge into target t using source s on (t.name=s.name) when matched then upd
ate set t.id=s.id when not matched then insert values(s.id,s.name);

2 rows merged.

SQL> merge into target T using (select id,name from source) S on (T.name=S.name)
when matched then update set t.id=s.id when not matched then insert values(s.id,
s.name);

2 rows merged.

In Oracle we no need to create any unique constraints.