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

Oracle DBMS SCHEDULER Examples

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

7 Steps to configure BDR replication in postgresql

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

vacuumlo - removing large objects orphans from a database PostgreSQL