如何在PostgreSQL中更新（合并、插入...重复更新时）？

Question

这里一个非常常见的问题是如何执行upsert，MySQL称之为insert...ON DUPLICATE update，标准支持将其作为merge操作的一部分。

鉴于PostgreSQL不直接支持它（在PG9.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时所谈论的。至关重要的是，在同一表上存在多个事务的情况下，任何方法都必须是安全的--要么使用显式锁定，要么以其他方式防止产生的竞争条件。

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

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

Answer 1

PostgreSQL 9.5和更新版本支持insert...在冲突（key）上做更新（和在冲突（key）上做任何事情)，即upsert。

与重复密钥更新的比较。

快速解释。

PostgreSQL没有任何内置的upsert（或merge)功能，并且在并发使用时很难高效地执行。

本文详细讨论了这个问题。

通常，您必须在以下两个选项中进行选择：

null

许多尝试解决此问题的方案都没有考虑回滚，因此导致更新不完整。两个交易相互竞争；其中一个成功地插入；另一个则获得重复键错误，并执行更新。update块等待insert回滚或提交。当它回滚时，update条件重新检查匹配零行，因此即使update提交，它实际上也没有完成预期的upsert。您必须检查结果行计数，并在必要的地方重新尝试。

一些尝试的解决方案也没有考虑选择的种族。如果你尝试简单明了的：

-- 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;

然后，当两个同时运行时，有几种故障模式。一个是已经讨论过的与更新重新检查有关的问题。另一种情况是同时更新，匹配零行并继续。然后它们都执行exists测试，该测试发生在insert之前。两者都获得零行，因此都执行insert。一个失败，出现重复的密钥错误。

有时您希望进行批量更新，其中您有一个新的数据集，您希望将其合并到旧的现有数据集中。这比单独的行upsert效率高得多，在实际情况下应该优先使用。

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

>

创建临时表

提交，释放锁。

例如，对于问题中给出的示例，使用多值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;

null

SQL-standardmerge实际上定义的并发语义很差，不适合在不首先锁定表的情况下进行upsert。

对于数据合并来说，这是一个非常有用的OLAP语句，但对于并发安全的upsert来说，它实际上并不是一个有用的解决方案。对于使用其他DBMSE的人来说，有很多建议可以将merge用于upserts，但这实际上是错误的。

• 在MySQL中插入...重复键更新
• 从MS SQL Server合并（但请参见上面关于合并问题）
• 从Oracle合并（但请参阅上面关于合并问题）