如何在PostgreSQL中的UPSERT(MERGE,INSERT … ON DUPLICATE UPDATE)?

前端之家收集整理的这篇文章主要介绍了如何在PostgreSQL中的UPSERT(MERGE,INSERT … ON DUPLICATE UPDATE)?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
这里的一个常见问题是如何做一个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 herethe 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;

相关阅读

> UPSERT wiki page
> UPSERTisms in Postgres
> Insert,on duplicate update in PostgreSQL?
> http://petereisentraut.blogspot.com/2010/05/merge-syntax.html
> Upsert with a transaction
> Is SELECT or INSERT in a function prone to race conditions?
> SQL MERGE on the PostgreSQL wiki
> Most idiomatic way to implement UPSERT in Postgresql nowadays

MERGE怎么样?

sql标准MERGE实际上具有较差的并发语义,并且不适合不首先锁定表的上升。

这是一个非常有用的OLAP语句,用于数据合并,但它并不是一个有用的并发安全上升的解决方案。有很多建议,使用其他DBMS的人使用MERGE的upserts,但它实际上是错误的。

其他DB:

> INSERT ... ON DUPLICATE KEY UPDATE in MySQL
> MERGE from MS SQL Server(但参见上面的MERGE问题)
> MERGE from Oracle(但参见上面的MERGE问题)

原文链接:https://www.f2er.com/postgresql/193955.html

猜你在找的Postgre SQL相关文章