迁移 Oracle 的 Merge into 语句，从未如此轻松！

PostgreSQL MERGE 语句简介

PostgreSQL 15 引入了MERGE语句，可将INSERT、UPDATE和DELETE操作合并到一个语句中，来简化数据操作。MERGE语句通常被称为UPSERT语句。

如果使用的是早期版本的 PostgreSQL，则应考虑使用INSERT... ON CONFLICT语句。

下面是MERGE语句的语法：

MERGE INTO target_table
USING source_query
ON merge_condition
WHEN MATCH [AND condition] THEN {merge_update | merge_delete | DO NOTHING }
WHEN NOT MATCHED [AND condition] THEN { merge_insert | DO NOTHING };

在此语法中：

• target_table是要修改数据的表（INSERT、UPDATE和DELETE）。

• source_query是一个源表或 SELECT 语句，为合并操作提供数据。

• ON merge_condition：此子句指定匹配源表和目标表之间的行的条件。

• WHEN MATCHED THEN：此子句定义了匹配合并条件的行上的操作语句。该条件为执行 update 或 delete 语句提供了附加条件。如果您不想对匹配行执行任何操作，可以使用DO NOTHING选项。

• WHEN NOT MATCHED THEN：此子句定义了不匹配合并条件的行上的操作语句。您可以指定 insert 语句向目标表添加新行，也可以使用DO NOTHING忽略不匹配的行。

请注意，merge_insert、merg_update和merge_delete语句，与常规的INSERT、UPDATE和DELETE语句略有不同。

merge_insert是不带表名的INSERT语句：

INSERT (column1, ...)
VALUES(value1,...);

merge_update语句是不带表名和WHERE子句的UPDATE语句：

UPDATE SET
   column1 = value1, 
   column2 =value2,
   ...;

merge_delete语句就是简单的DELETE关键字：

DELETE

成功完成后，MERGE语句会返回下面的命令标记：

MERGE total_count

在此标记中，total_acount是插入、更新或删除的行的总数。如果total_count为零，则意味着没有行被更改。

MERGE语句对于在表之间同步数据很有用，允许您有效地让目标表与源表中的更改保持同步。

PostgreSQL MERGE 语句示例

让我们来探索一些使用MERGE语句的示例。

0) 设置样例表

首先，创建两个表，名为leads和customers：

CREATE TABLE leads(
    lead_id serial PRIMARY key,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    active bool NOT NULL DEFAULT TRUE
);
CREATE TABLE customers(
    customer_id serial PRIMARY key,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    active bool NOT NULL DEFAULT TRUE
);

我们将使用MERGE语句来合并两个表的数据。

1) 使用 PostgreSQL MERGE 语句将源表的行插入目标表

首先，插入两行到leads表中：

INSERT INTO leads(name, email)
VALUES
   ('John Doe', 'john.doe@gmail.com'),
   ('Jane Doe', 'jane.doe@yahoo.com')
RETURNING *;

输出：

lead_id |   name   |       email        | active
---------+----------+--------------------+--------
       1 | John Doe | john.doe@gmail.com | t
       2 | Jane Doe | jane.doe@yahoo.com | t
(2 rows)

第二步，使用MERGE语句将leads表中的行插入到customers表中：

MERGE INTO customers c
USING leads l ON c.email = l.email
WHEN NOT MATCHED THEN 
   INSERT (name, email)
   VALUES(l.name, l.email);

在此语句中，我们使用了leads和customers表的email列作为合并条件。

如果leads表中的email与customers表中的email不匹配，则MERGE语句会将新行插入到customers表中。

输出：

MERGE 2

输出表明已成功插入两行。

第三步，从customers表中检索数据：

SELECT * FROM customers;

输出：

customer_id |   name   |       email        | active
-------------+----------+--------------------+--------
           1 | John Doe | john.doe@gmail.com | t
           2 | Jane Doe | jane.doe@yahoo.com | t
(2 rows)

2) 使用 MERGE 语句将源表的行更改和插入目标表

首先，插入一个新行到leads表中，并更新 id 为 2 的行的name：

INSERT INTO leads(name, email)
VALUES('Alice Smith', 'alice.smith@outlook.com');

UPDATE leads
SET name = 'Jane Gate'
WHERE lead_id = 2;

第二步，从leads表中检索数据：

SELECT * FROM leads
ORDER BY id;

输出：

lead_id |    name     |          email          | active
---------+-------------+-------------------------+--------
       1 | John Doe    | john.doe@gmail.com      | t
       2 | Jane Gate   | jane.doe@yahoo.com      | t
       3 | Alice Smith | alice.smith@outlook.com | t
(3 rows)

leads表中现在有一个 id 为 2 的修改过的行，和一个 id 为 3 的新行。

第三步，将leads表中的新行添加到customers表中，并对更新的行更改name和email：

MERGE INTO customers c
USING leads l ON c.email = l.email
WHEN NOT MATCHED THEN 
   INSERT (name, email)
   VALUES(l.name, l.email)
WHEN MATCHED THEN
   UPDATE SET 
      name = l.name, 
      email = l.email;

该MERGE语句会匹配email列，将新行插入到customers表中，并根据leads表中的数据更新customers表中的现有行。

输出：

MERGE 3

输出表示已修改三行：

• • 插入了一个新行。
• • 更新了两个匹配的行。

3) 使用 MERGE 语句更改、插入和删除行

首先，在leads表中插入一个新行：

INSERT INTO leads(name, email)
VALUES('Bob Climo', 'blob.climo@gmail.com');

第二步，将leads表中 id 为 2 的行的active列设置为false：

UPDATE leads
SET active = false
WHERE lead_id = 2;

第三步，将leads表中 id 为 1 的行的email列设置为 “john.doe@hotmail.com”：

UPDATE leads
SET email = 'john.doe@hotmail.com'
WHERE lead_id = 1;

第四步，从leads表中检索数据：

SELECT * FROM leads
ORDER BY lead_id;

输出：

lead_id |    name     |          email          | active
---------+-------------+-------------------------+--------
       1 | John Doe    | john.doe@hotmail.com    | t
       2 | Jane Gate   | jane.doe@yahoo.com      | f
       3 | Alice Smith | alice.smith@outlook.com | t
       4 | Bob Climo   | blob.climo@gmail.com    | t
(4 rows)

第五步，将leads表中的新行插入到customers表中，从customers表中删除active为false的行，并对active为true的行更新name和email：

MERGE INTO customers c
USING leads l ON c.email = l.email
WHEN NOT MATCHED THEN 
   INSERT (name, email)
   VALUES(l.name, l.email)
WHEN MATCHED AND l.active = false THEN
   DELETE
WHEN MATCHED AND l.active = true THEN
   UPDATE SET 
      name = l.name, 
      email = l.email;

输出：

MERGE 4

最后，从customers表中检索行：

SELECT * FROM customers;

输出：

customer_id |    name     |          email          | active
-------------+-------------+-------------------------+--------
           1 | John Doe    | john.doe@gmail.com      | t
           3 | Alice Smith | alice.smith@outlook.com | t
           4 | Bob Climo   | blob.climo@gmail.com    | t
           5 | John Doe    | john.doe@hotmail.com    | t
(4 rows)

总结

使用MERGE语句有条件地插入、更新和删除一个表中的行。