五月 25, 2024
摘要：在本教程中，您将学习如何使用 PostgreSQL MERGE语句，有条件地插入、更新和删除一个表中的行。
目录
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语句有条件地插入、更新和删除一个表中的行。