如何在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

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

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

Answer 1

null

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

这就是为什么需要重试循环。您可能认为使用clever SQL可以防止重复的密钥错误或丢失更新，但您做不到。您需要检查行计数或处理重复键错误（取决于选择的方法），然后重试。

请不要为这个卷你自己的解决方案。就像消息队列一样，这可能是错误的。

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

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合并（但请参阅上面关于合并问题）