From PostgreSQL 9.1, onwards user can implement the feature of Merge using the writable CTE (Common Table Expressions)
WITH provides a way to write auxiliary statements for use in a larger query. This 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 clause WITH witself is attached to a primary statement that can also cause a SELECT,INSERT,UPDATE or DELETE
Created 2 tables and insert some data into it. Now merge the 2 tables
[root@ip--- ~]# psql tejidatabase
psql (9.2.4.8)
Type "help" for help.
tejidatabase=# select * from testmerge;
pid | age | name
-----+-----+--------
2 | 48 | RAM
4 | 61 | SHYAM
6 | 85 | SONIA
8 | 44 | RAHUL
10 | 34 | MAMTA
12 | 45 | SURJIT
14 | 21 | ISHIKA
16 | 19 | IPSA
(8 rows)
tejidatabase=# select * from merge2;
pid | age | name
-----+-----+--------
18 | 56 | YASUDA
8 | 0 | RAHUL
14 | 5 | ISHIKA
(3 rows)
tejidatabase=# WITH upsert as (update merge2 m set age=d.age+100 ,name=d.name from testmerge d where m.pid=d.pid RETURNING m.*) insert into merge2 select a.pid,a.age,'NEW' from testmerge a where a.pid not in ( select b.pid from upsert b);
INSERT 0 6
tejidatabase=# select * from merge2;
pid | age | name
-----+-----+--------
18 | 56 | YASUDA
8 | 144 | RAHUL
14 | 121 | ISHIKA
2 | 48 | NEW
4 | 61 | NEW
6 | 85 | NEW
10 | 34 | NEW
12 | 45 | NEW
16 | 19 | NEW
(9 rows)
tejidatabase=#
As you can see all the rows of test merge are now added in merge2 with name=’NEW’ and the matching pid of test merge and merge2, the ages have been added by 100.
The magic of Writable CTE which can make UPSERT in PostgreSQL
:-)
