Merge in Postgresql

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)
   UPDATE SET column1 = value1 [, column2 = value2 …]
   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.


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;


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

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 ( when matched then upd
ate set when not matched then insert values(,;

2 rows merged.

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

2 rows merged.

In Oracle we no need to create any unique constraints.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s