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？

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

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     |
-- +--+--------------+