使用ON CONFLICT从INSERT返回行，而无需更新

admin

使用ON CONFLICT从INSERT返回行，而无需更新

sql

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

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仅返回插入或更新的行。因此，在上面的示例中，它不会返回任何内容。为了使它发挥所需的功能，我实际上需要：

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

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

不允许（我的）期望行为的原因是什么？
有没有更优雅的方法可以做到这一点？

阅读 79

2021-05-10

共1个答案

admin

这是反复出现的问题 SELECT or INSERT ，与UPSERT相关（但与UPSERT不同）。Postgres
9.5中的新UPSERT功能仍然起作用。

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;

这样，您实际上并不需要编写新的行版本。

我假设您知道在PostgresUPDATE中，由于其MVCC模型，每个人都会写入该行的新版本-
即使name设置为与以前相同的值。这将使操作更加昂贵，在某些情况下增加可能的并发问题/锁争用，并另外膨胀表。

但是，在 竞赛条件下 仍然存在一个 极小的情况 。并发事务可能添加了冲突的行，该行在同一条语句中尚不可见。然后INSERT
， SELECT空了出来。

单行UPSERT的正确解决方案：

SELECT或INSERT函数是否易于出现竞争状况？

没有并发写入负载

如果不可能进行并发写入（来自不同的会话），则不需要锁定行，可以简化以下操作：

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;

2021-05-10