用postrgres做幂等插入 [英] Doing an idempotent insert with postrgres

问题描述

我想要类似的东西

INSERT VALUES(1,2,3) INTO sometable ON CONFLICT DO NOTHING IF EXACTLY SAME ROW

所以我想要以下行为：

#CREATE TABLE sometable (a int primary key, b int, c int);

CREATE TABLE
 #INSERT INTO sometable (1,2,3) ON CONFLICT DO NOTHING IF EXACTLY SAME ROW
INSERT 0 1
 #INSERT INTO sometable (1,2,3) ON CONFLICT DO NOTHING IF EXACTLY SAME ROW
INSERT 0 0
 #INSERT INTO sometable (1,3,2) ON CONFLICT DO NOTHING IF EXACTLY SAME ROW
ERROR:  duplicate key value violates unique constraint "sometable_pkey"
DETAIL:  Key (a)=(1) already exists.

这似乎是很自然的事情，因为客户端应用程序无法假设它会知道插入成功（如果postgres或客户端崩溃或网络失败，则可能已处理了请求，但客户端从未收到确认）。因此，任何编写良好的应用程序都需要以某种方式处理这种情况。

Desiring this seems a very natural thing, because a client application can't assume it will know if an insert succeeded (if postgres or the client crashes or the network fails, the request might have been processed but the client never receives confirmation). So any well written application needs to deal with this case somehow.

但是，我发现实现这一目标的最糟糕的方法仍然很烦人：

However, the least bad way of achieving this that I have found is still very annoying:

INSERT INTO sometable (a,b,c) VALUES(1,2,3) ON CONFLICT(a) UPDATE set sometable.b=2 WHERE sometable.b=2 AND sometable.c=3;

换句话说，请执行无操作更新，但前提是值必须是您所拥有的

In other words, do a no-op update, but only if the values are what you would have inserted and then throw an error if 0 rows (rather than 1) where touched.

是否有更好的方法？

推荐答案

您可以根据选择使用INSERT：

You can use an INSERT based on a select:

insert into sometable
select *
from ( values (1,2,3) ) as data(a,b,c)
where not exists (select *
                  from sometable
                  where data = sometable);

是的，条件其中数据=稳定表在Postgres中有效，并且仅比较所有列。

Yes, the condition where data = sometable is valid in Postgres and simply compares all columns.

这也可以扩展为多行：

insert into sometable
select *
from ( 
  values 
    (1,2,3),
    (4,5,6),
    (7,8,9)
) as data(a,b,c)
where not exists (select *
                  from sometable
                  where data = sometable);

这不能防止PK违规错误（如发生冲突可以）（如果从多个事务完成）。您仍然需要处理这些错误。

This does not prevent PK violation errors (as on conflict does) if done from multiple transactions though. You still need to handle those errors.

这篇关于用postrgres做幂等插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！