PostgreSQL Merge

• MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent.
• To implement this cleanly requires that the table have a unique index so duplicate checking can be easily performed. It is possible to do it without a unique index if we require the user to LOCK the table before the MERGE.
• MERGE is often used interchangeably with the term UPSERT. 
• UPSERT functionality will be in the PostgreSQL 9.5 release
• PostgreSQL 9.1, now has Writable CTE. WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE.

For PostgreSQL 9.1:

postgres=# create table myTab
  (pid numeric, sales numeric, status varchar(6));
 
postgres=# create table myTab2
  (pid numeric, sales numeric, status varchar(6));
 
insert into myTab2 values(1,12,'CURR');
insert into myTab2 values(2,13,'NEW' );
insert into myTab2 values(3,15,'CURR');

postgres=# select * from myTab2;
 pid | sales | status 
-----+-------+--------
   1 |    12 | CURR
   2 |    13 | NEW
   3 |    15 | CURR
(3 rows)


insert into myTab  values(2,24,'CURR');
insert into myTab  values(3, 0,'OBS' );
insert into myTab  values(4,42,'CURR');

postgres=# select * from myTab;
 pid | sales | status 
-----+-------+--------
   2 |    24 | CURR
   3 |     0 | OBS
   4 |    42 | CURR
(3 rows)

In PostgreSQL 9.1, with writable CTE:

postgres=# WITH upsert as
(update mytab2 m set sales=m.sales+d.sales, status=d.status from mytab d where m.pid=d.pid
  RETURNING m.*
)
insert into mytab2 select a.pid, a.sales,'NEW' from mytab a where a.pid not in (select b.pid from upsert b);
 
INSERT 0 1
postgres=# select * from mytab2;
 pid | sales | status
-----+-------+--------
   1 |    12 | CURR
   2 |    37 | CURR
   3 |    15 | OBS
   4 |    42 | NEW