如何在PostgreSQL中的UPSERT(MERGE,INSERT … ON DUPLICATE UPDA

发布时间：2020-12-13 16:55:32 所属栏目：百科来源：网络整理

导读：这里的一个常见问题是如何做一个upsert，这是什么MySQL调用INSERT … ON DUPLICATE UPDATE和标准支持作为MERGE操作的一部分。鉴于PostgreSQL不直接支持它(在pg 9.5之前)，你怎么做呢？考虑以下： CREATE TABLE testtable ( id integer PRIMARY KEY,somedata

这里的一个常见问题是如何做一个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');

现在想象你想要“upsert”元组(2，’Joe’)，(3，’Alan’)，所以新的表内容将是：

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

这是人们在讨论一个upsert时。至关重要的是，任何方法必须在同一个表上工作的多个事务存在时是安全的 – 通过使用显式锁定，或者以其他方式保护结果竞争条件。

这个主题在Insert,on duplicate update in PostgreSQL?广泛讨论，但这是关于MySQL语法的替代，并且它增长了一段时间不相干的细节。我正在努力确定的答案。

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

9.5及更高版本：

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

Comparison with ON DUPLICATE KEY UPDATE。

有关用法，请参见the manual – 具体来说，语法图中的conflict_action子句和the explanatory text。

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

The commit adding the feature is here和the discussion around its development is here。

如果你是9.5，不需要向后兼容，你可以立即停止阅读。

9.4及以上：

PostgreSQL没有任何内置的UPSERT(或MERGE)设施，并且在面对并发使用时有效率是非常困难的。

This article discusses the problem in useful detail。

一般来说，您必须在两个选项之间进行选择：

>重试循环中的各个插入/更新操作;要么
>锁定表并执行批量合并

单个行重试循环

如果想要许多连接同时尝试执行插入，在重试循环中使用单独的行上行是合理的选择。

The PostgreSQL documentation contains a useful procedure that’ll let you do this in a loop inside the database.它保护丢失的更新和插入比赛，不像大多数天真的解决方案。它只能在READ COMMITTED模式下工作，只有在事务中做的唯一事情才是安全的。如果触发器或辅助唯一键导致唯一违反，则该函数将无法正常工作。

这个策略是非常低效的。每当实用，你应该排队工作，并做一个批量upsert如下所述。

许多尝试解决此问题的解决方案无法考虑回滚，因此它们导致不完整的更新。两个交易彼此竞争;其中一个成功插入;另一个获取重复的键错误，并做一个UPDATE。 UPDATE阻塞等待INSERT回滚或提交。当它回滚时，UPDATE条件重新检查匹配零行，所以即使UPDATE提交它没有实际完成你期望的upsert。您必须检查结果行计数，并在必要时重试。

一些尝试的解决方案也无法考虑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丢失更新，但你不能。您需要检查行计数或处理重复的键错误(取决于所选的方法)，然后重试。

请不要为此自己解决方案。就像消息队列，这可能是错的。

带锁的批量上载

有时你想做一个批量上升，你有一个新的数据集，你想合并到一个较旧的现有数据集。这比单独的行上行文件效率??高得多，并且在实际应用时应该是优选的。

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

> CREATE a TEMPORARY表
> COPY或批量插入新数据到临时表中
>锁定目标表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,(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;