Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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





Comments

Popular posts from this blog

7 Steps to configure BDR replication in postgresql

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

apt-add-repository

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory