admin

如何在 PostgreSQL 中 UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE)？

sql

这里一个非常常见的问题是如何进行 upsert，这是 MySQL 调用INSERT ... ON DUPLICATE UPDATE和标准支持的MERGE操作的一部分。

鉴于 PostgreSQL 不直接支持它（在 pg 9.5 之前），你如何做到这一点？考虑以下：

CREATE TABLE testtable (
    id integer PRIMARY KEY,
    somedata text NOT NULL
);

INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');

现在想象你想“更新”元组(2, 'Joe'), (3, 'Alan')，所以新的表格内容是：

(1, 'fred'),
(2, 'Joe'),    -- Changed value of existing tuple
(3, 'Alan')    -- Added new tuple

这就是人们在讨论upsert. 至关重要的是，在存在多个事务处理同一个表的情况下，任何方法都必须是安全的——要么使用显式锁定，要么以其他方式防御由此产生的竞争条件。

这个话题在Insert 上广泛讨论，关于 PostgreSQL 中的重复更新？，但那是关于 MySQL 语法的替代方案，随着时间的推移，它增加了一些不相关的细节。我正在寻找明确的答案。

这些技术也可用于“如果不存在则插入，否则什么都不做”，即“插入…重复键忽略”。

阅读 248

2021-07-01

共1个答案

admin

9.5 及更新版本：

PostgreSQL 9.5 和更新的支持INSERT ... ON CONFLICT (key) DO UPDATE（和ON CONFLICT (key) DO NOTHING），即 upsert。

有关用法，请参阅手册- 特别是语法图中的conflict_action子句和解释性文本。

与下面给出的 9.4 及更早版本的解决方案不同，此功能适用于多个冲突行，并且不需要排他锁定或重试循环。

如果您使用的是 9.5 并且不需要向后兼容，您现在可以停止阅读。

9.4 及以上：

PostgreSQL 没有任何内置UPSERT（或MERGE）工具，在并发使用的情况下高效地做到这一点是非常困难的。

通常，您必须在两个选项之间进行选择：

重试循环中的单个插入/更新操作；或者
锁定表并进行批量合并

单行重试循环

如果您希望多个连接同时尝试执行插入，则在重试循环中使用单个行更新插入是合理的选择。

PostgreSQL 文档包含一个有用的过程，可让您在数据库内的循环中执行此操作。与大多数幼稚的解决方案不同，它可以防止丢失更新和插入竞争。不过，它只能在READ COMMITTED模式下工作，并且只有当它是您在交易中唯一要做的事情时才是安全的。如果触发器或辅助唯一键导致唯一违规，该功能将无法正常工作。

这种策略非常低效。只要可行，您应该将工作排队并进行如下所述的批量 upsert。

许多尝试解决此问题的方法都没有考虑回滚，因此会导致更新不完整。两笔交易相互竞争；其中一个成功了INSERT；另一个收到重复的密钥错误并UPDATE改为执行。UPDATE等待INSERT回滚或提交的块。当它回滚时，UPDATE条件重新检查匹配零行，因此即使UPDATE提交它实际上并没有完成您期望的更新插入。您必须检查结果行计数并在必要时重试。

一些尝试的解决方案也没有考虑 SELECT 比赛。如果您尝试显而易见且简单的方法：

-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.

BEGIN;

UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;

-- Remember, this is WRONG. Do NOT COPY IT.

INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);

COMMIT;

那么当两个同时运行时，会出现多种故障模式。一个是已经讨论过的更新重新检查问题。另一个是两者UPDATE同时匹配零行并继续。然后，他们都做EXISTS测试，这恰好之前的INSERT。两者都得到零行，因此两者都执行INSERT. 一个因重复密钥错误而失败。

这就是您需要重试循环的原因。您可能认为使用巧妙的 SQL 可以防止重复键错误或丢失更新，但您不能。您需要检查行数或处理重复的键错误（取决于选择的方法）并重试。

请不要为此推出您自己的解决方案。就像消息队列一样，它可能是错误的。

带锁的批量插入

有时您想要进行批量 upsert，其中您有一个新数据集，您希望将其合并到旧的现有数据集中。这大大超过各行upserts更高效，更应是首选，只要实用。

在这种情况下，您通常遵循以下过程：

CREATE一张TEMPORARY桌子
COPY 或将新数据批量插入临时表
LOCK目标表IN EXCLUSIVE MODE。这允许其他事务SELECT，但不能对表进行任何更改。
UPDATE ... FROM使用临时表中的值执行现有记录；
执行INSERT目标表中尚不存在的行；
COMMIT，解除锁定。

例如，对于问题中给出的示例，使用多值INSERT填充临时表：

BEGIN;

CREATE TEMPORARY TABLE newvals(id integer, somedata text);

INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');

LOCK TABLE testtable IN EXCLUSIVE MODE;

UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;

INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;

COMMIT;

2021-07-01