MERGE INTO 语句

MERGE INTO 语句是 INSERT INTO ... ON CONFLICT 的一种替代方案。由于它允许自定义匹配条件，因此不需要主键。当目标表没有主键约束时，它是实现 upsert（INSERT + UPDATE）的非常实用的替代方式。

示例

首先，创建一个简单的表。

CREATE TABLE people (id INTEGER, name VARCHAR, salary FLOAT);
INSERT INTO people VALUES (1, 'John', 92_000.0), (2, 'Anna', 100_000.0);

最简单的 upsert 方式是更新或插入整行数据。

MERGE INTO people
    USING (
        SELECT
            unnest([3, 1]) AS id,
            unnest(['Sarah', 'John']) AS name,
            unnest([95_000.0, 105_000.0]) AS salary
    ) AS upserts
    ON (upserts.id = people.id)
    WHEN MATCHED THEN UPDATE
    WHEN NOT MATCHED THEN INSERT;

FROM people
ORDER BY id;

id	name	salary
1	John	105000.0
2	Anna	100000.0
3	Sarah	95000.0

在前一个示例中，如果 id 匹配，我们会更新整行。不过，另一种常见模式是接收包含若干键及其变更值的 change set。这种场景很适合使用 SET。如果匹配条件使用了源表和目标表中同名的列，则可以在匹配条件中使用关键字 USING。

MERGE INTO people
    USING (
        SELECT
            1 AS id, 
            98_000.0 AS salary
    ) AS salary_updates
    USING (id)
    WHEN MATCHED THEN UPDATE SET salary = salary_updates.salary;

FROM people
ORDER BY id;

id	name	salary
1	John	98000.0
2	Anna	100000.0
3	Sarah	95000.0

另一种常见模式是接收一个待删除行的 delete set，其中可能只包含要删除行的 id。

MERGE INTO people
    USING (
        SELECT
            1 AS id, 
    ) AS deletes
    USING (id)
    WHEN MATCHED THEN DELETE;

FROM people
ORDER BY id;

id	name	salary
2	Anna	100000.0
3	Sarah	95000.0

MERGE INTO 还支持更复杂的条件。例如，对于给定的 delete set，我们可以只删除 salary 大于或等于某个值的行。

MERGE INTO people
    USING (
        SELECT
            unnest([3, 2]) AS id, 
    ) AS deletes
    USING (id)
    WHEN MATCHED AND people.salary >= 100_000.0 THEN DELETE;

FROM people
ORDER BY id;

id	name	salary
3	Sarah	95000.0

如有需要，Goose 也支持多个 UPDATE 和 DELETE 条件。RETURNING 子句可用于指示哪些行受 MERGE 语句影响。

-- 把 John 加回来！
INSERT INTO people VALUES (1, 'John', 105_000.0);

MERGE INTO people
    USING (
        SELECT
            unnest([3, 1]) AS id,
            unnest([89_000.0, 70_000.0]) AS salary
    ) AS upserts
    USING (id)
    WHEN MATCHED AND people.salary < 100_000.0 THEN UPDATE SET salary = upserts.salary
    -- Second update or delete condition
    WHEN MATCHED AND people.salary > 100_000.0 THEN DELETE
    WHEN NOT MATCHED THEN INSERT BY NAME
    RETURNING merge_action, *;

merge_action	id	name	salary
UPDATE	3	Sarah	89000.0
DELETE	1	John	105000.0

在某些情况下，如果源数据不满足某个条件，你可能希望执行不同的操作。例如，如果我们期望“源中不存在的数据也不应存在于目标中”：

CREATE TABLE target AS
    SELECT unnest([1,2]) AS id;

MERGE INTO target
    USING (SELECT 1 AS id) source
    USING (id)
    WHEN MATCHED THEN UPDATE
    WHEN NOT MATCHED BY SOURCE THEN DELETE
    RETURNING merge_action, *;

merge_action	id
UPDATE	1
DELETE	2

也可以指定 WHEN NOT MATCHED BY TARGET。不过，正如你可能预期的那样，其行为与 WHEN NOT MATCHED 相同，因为默认在指定条件时是以目标表为参照。

语法