使用ON CONFLICT从INSERT返回行，而无需更新 [英] Return rows from INSERT with ON CONFLICT without needing to update

问题描述

我遇到的情况是，我经常需要从具有唯一约束的表中获取一行，如果不存在，则创建它并返回。
例如，我的表可能是：

I have a situation where I very frequently need to get a row from a table with a unique constraint, and if none exists then create it and return. For example my table might be:

CREATE TABLE names(
    id SERIAL PRIMARY KEY,
    name TEXT,
    CONSTRAINT names_name_key UNIQUE (name)
);

其中包含：

id | name
 1 | bob 
 2 | alice

然后我要：

 INSERT INTO names(name) VALUES ('bob')
 ON CONFLICT DO NOTHING RETURNING id;

或者也许：

 INSERT INTO names(name) VALUES ('bob')
 ON CONFLICT (name) DO NOTHING RETURNING id

并返回bob的ID 1 。但是， RETURNING 仅返回插入或更新的行。因此，在上面的示例中，它不会返回任何内容。为了使其具有所需的功能，我实际上需要：

and have it return bob's id 1. However, RETURNING only returns either inserted or updated rows. So, in the above example, it wouldn't return anything. In order to have it function as desired I would actually need to:

INSERT INTO names(name) VALUES ('bob') 
ON CONFLICT ON CONSTRAINT names_name_key DO UPDATE
SET name = 'bob'
RETURNING id;

这似乎有点麻烦。我想我的问题是：

which seems kind of cumbersome. I guess my questions are:

1. 不允许（我的）期望行为的原因是什么？

1. What is the reasoning for not allowing the (my) desired behaviour?

是否有更优雅的方法？

推荐答案

这是 SELECT或INSERT 的重复出现的问题，与（但与UPSERT不同。 Postgres 9.5中的新UPSERT功能仍然有用。

It's the recurring problem of SELECT or INSERT, related to (but different from) an UPSERT. The new UPSERT functionality in Postgres 9.5 is still instrumental.

WITH ins AS (
   INSERT INTO names(name)
   VALUES ('bob')
   ON     CONFLICT ON CONSTRAINT names_name_key DO UPDATE
   SET    name = NULL
   WHERE  FALSE      -- never executed, but locks the row
   RETURNING id
   )
SELECT id FROM ins
UNION  ALL
SELECT id FROM names
WHERE  name = 'bob'  -- only executed if no INSERT
LIMIT  1;

通过这种方式，您实际上并不需要编写新的行版本。

This way you do not actually write a new row version without need.

^{我假设您知道在Postgres中，每个 UPDATE 都会由于该行的 MVCC模型-即使 name 是设置为与以前相同的值。这将使操作更加昂贵，在某些情况下增加可能的并发问题/锁争用，并导致表膨胀。}

^{I assume you are aware that in Postgres every UPDATE writes a new version of the row due to its MVCC model - even if name is set to the same value as before. This would make the operation more expensive, add to possible concurrency issues / lock contention in certain situations and bloat the table additionally.}

但是，仍然有用于比赛条件的小巧情况。并发事务可能添加了冲突的行，该行在同一语句中尚不可见。然后 INSERT 和 SELECT 变成空。

However, there is still a tiny corner case for a race condition. Concurrent transactions may have added a conflicting row, which is not yet visible in the same statement. Then INSERT and SELECT come up empty.

单行UPSERT的正确解决方案：

Proper solution for single-row UPSERT:

• 在功能中容易发生竞争的条件是SELECT或INSERT吗？

• Is SELECT or INSERT in a function prone to race conditions?

批量UPSERT的一般解决方案：

General solutions for bulk UPSERT:

• 如何在PostgreSQL中在ON CONFLICT中使用RETURNING？

• How to use RETURNING with ON CONFLICT in PostgreSQL?

如果并发写入（来自其他会话）是不可能的，您不需要锁定行并可以简化：

If concurrent writes (from a different session) are not possible you don't need to lock the row and can simplify:

WITH ins AS (
   INSERT INTO names(name)
   VALUES ('bob')
   ON     CONFLICT ON CONSTRAINT names_name_key DO NOTHING  -- no lock needed
   RETURNING id
   )
SELECT id FROM ins
UNION  ALL
SELECT id FROM names
WHERE  name = 'bob'  -- only executed if no INSERT
LIMIT  1;

这篇关于使用ON CONFLICT从INSERT返回行，而无需更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！