postgresql - INSERT ON DUPLICATE 语句返回什么？

Question

在 Postgresql 中，当插入带有一个on conflict和一个returning子句的行时，返回什么？插入的行？插入前的行？如果没有插入行怎么办？

考虑使用以下语句... on conflict do nothing returning ...：

insert into test
values (:id, :name)
on conflict do nothing
returning id;

和以下声明... on conflict (...) do update ... returning ...：

insert into test
values (:id, :name)
on conflict (name) do update
set id = EXCLUDED.id + 10
returning id;

并假设id和name列都是唯一的。上面的语句会返回什么？

密切相关，我如何找到导致冲突的现有行的 ID？

PS：是的，我可以轻松地尝试一下。事实上，这就是我所做的（见答案）。很容易做出错误的猜测，很难记住答案并且重现实验的成本很高，所以我认为这个问题对其他人有用。

标签： postgresqlinsert-update

Answer 1

该returning子句始终返回写入表的信息（即插入或更新）。如果未发生写入，则不会返回任何行。

因此，如果要获取导致冲突的现有行的 id，则不能使用on conflict do nothing. 相反，您可以使用on conflict (...) do update虚拟更新来引发写入，该写入实际上不会修改现有行的内容，如下所示：

insert into test
values (4, 'singleton')
on conflict (name) do update
set id = test.id -- force a write, but don't change anything
returning id;

但请注意，这种人为写入可能仍会产生不良副作用，尤其是在触发器到位的情况下。即使数据没有改变，您也会生成新的元组。

实验证明：

drop table if exists test;
create table test (
    id int primary key,
    name text unique
);

insert into test
values (1, 'singleton');

select * from test;
-- +--+---------+
-- |id|name     |
-- +--+---------+
-- |1 |singleton|
-- +--+---------+

insert into test
values (2, 'singleton')
on conflict do nothing
returning id;
-- result: empty

insert into test
values (2, 'something else')
on conflict (name) do update
    set id = EXCLUDED.id + 10
returning id;
-- result:
-- +--+
-- |id|
-- +--+
-- |2 |
-- +--+

insert into test
values (3, 'singleton')
on conflict (name) do update
set id = EXCLUDED.id + 10
returning id;
-- result:
-- +--+
-- |id|
-- +--+
-- |13|
-- +--+

insert into test
values (4, 'singleton')
on conflict (name) do update
set id = test.id
returning id;
-- result:
-- +--+
-- |id|
-- +--+
-- |13|
-- +--+

select * from test;
-- result:
-- +--+--------------+
-- |id|name          |
-- +--+--------------+
-- |2 |something else|
-- |13|singleton     |
-- +--+--------------+