An overview of the

The MERGE statement is a new syntax added to Oracle9i to MERGE UPDATE and INSERT statements. The MERGE statement is used to query one table or subquery based on the join conditions of another table. If the join conditions match, UPDATE the other table. If the join conditions do not match, INSERT the other table. This syntax does all the work in a single full table scan, which is more efficient than INSERT+UPDATE

PostgreSQL does not support this syntax directly, but PostgreSQL can use the method WITH Queries (Common Table Expressions) to implement the same functionality.

Let’s take a look

The statement,

SQL > merge tablespace test1; merge tablespace test2; merge tablespace test1

WITH upsert AS ( UPDATE test1 SET col1 = test2.col1 FROM test2 WHERE test1.id = test2.id RETURNING test1.* ) INSERT INTO  test01 SELECT * FROM test2 WHERE NOT EXISTS ( SELECT 1 FROM upsert b WHERE test2.id = b.id );Copy the code

Select * from POSTgresQL on ————RETURNING an UPDATE result set (select primary key FROM POSTgresQL on ————RETURNING) These overlaps are ignored using where not exists. This merges the data

A small test

Built two tables

postgres=# create table test1(id int primary key,name text);
CREATE TABLE
postgres=# 
postgres=# create table test2(id int primary key,name text);
CREATE TABLECopy the code

Partial data overlap

postgres=# select * from test1;
 id | name  
----+-------
  1 | aaaaa
  2 | aaaaa
  3 | aaaaa
  4 | aaaaa
  5 | aaaaa
(5 rows)

postgres=# select * from test2;
 id | name  
----+-------
  4 | aaaaa
  5 | aaaaa
  6 | bbbbb
  7 | bbbbb
  8 | bbbbb
  9 | bbbbb
(6 rows)Copy the code

Execute merge statement

Updating test1 with test2 inserts data that is not in test1 and does not change it

postgres=# WITH upsert AS (
  UPDATE test1
  SET name = test2.name
  FROM test2
  WHERE test1.id = test2.id
  RETURNING test1.*
 )
INSERT INTO test1
SELECT *
FROM test2
WHERE NOT EXISTS (
 SELECT 1
 FROM upsert b
 WHERE test2.id = b.id
);
INSERT 0 4

postgres=# select * from test1;
 id | name  
----+-------
  1 | aaaaa
  2 | aaaaa
  3 | aaaaa
  4 | aaaaa
  5 | aaaaa
  6 | bbbbb
  7 | bbbbb
  8 | bbbbb
  9 | bbbbb
(9 rows)Copy the code

As you can see, the data has been updated

One caveat

One thing I noticed in my actual business scenario update was that the test2 table for the customer, the ID column is not the primary key, and there are many duplicates to see how many duplicate SQL columns there are in the ID column, if it is 0, then there are no duplicates

Select count(*) from users_purse where id in (select id from users_purse group by id having count(*) >1)Copy the code

If this happens, you might get an error because test1.ID is not repeatable, so you might need to reprocess distinct first.