如何在PostgreSQL中进行UPSERT（MERGE，INSERT…ON DUPLICATE UPDATE）？

Question

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

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

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

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

现在，假设你想“UPSERT”的元组(2, ‘Joe’)，(3, ‘Alan’)，因此新表的内容是：

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

那就是人们在讨论时谈论的话题upsert。至关重要的是，在存在多个事务处理同一个表的情况下，任何方法都必须是安全的-通过使用显式锁定或以其他方式抵御由此产生的竞争条件。

在插入中，关于PostgreSQL中的重复更新，对该主题进行了广泛的讨论。，但这是关于MySQL语法的替代方法，并且随着时间的推移，它已经增长了很多无关的细节。我正在努力确定答案。

这些技术还可用于“如果不存在则插入，否则不执行任何操作”，即“在重复键忽略时插入…”。

Answer 1

9.5及更高版本：PostgreSQL 9.5及更高版本的支持INSERT ... ON CONFLICT (key) DO UPDATE（和ON CONFLICT (key) DO NOTHING），即upsert。

与的比较ON DUPLICATE KEY UPDATE。

快速解释。

有关用法，请参见手册，特别是语法图中的conflict_action子句，以及说明性文字。

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

添加功能的提交在这里，关于功能开发的讨论在这里。

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

9.4及更高版本：
PostgreSQL没有任何内置UPSERT（或MERGE）功能，面对并发使用要高效地做到这一点非常困难。

本文详细讨论了该问题。

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

重试循环中的各个插入/更新操作；或者
锁定表并进行批量合并
个别行重试循环
如果您希望多个连接同时尝试执行插入操作，则在重试循环中使用单个行高位插入是合理的选择。

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

此策略效率很低。只要可行，您都应该将工作排入队列，并按如下所述进行批量追加。

许多尝试解决此问题的方法都没有考虑回滚，因此导致更新不完整。两笔交易相互竞争；他们的成功一个INSERTS; 另一个得到重复的密钥错误，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可以防止重复的键错误或更新丢失，但是您不能这样做。您需要检查行计数或处理重复的键错误（取决于所选方法），然后重试。

请不要为此使用您自己的解决方案。像消息队列一样，这可能是错误的。

带锁的批量更新
有时您想做一个批量上载，在这里您有一个新的数据集要合并成一个较旧的现有数据集。这大大超过各行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;