What is returned by an INSERT ON DUPLICATE statement?

Question

In Postgresql, when inserting rows with an on conflict and a returning clause, what is returned? The row that was inserted? The row before insertion? What if no row was inserted?

Consider the following statement using ... on conflict do nothing returning ...:

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

and the following statement ... on conflict (...) do update ... returning ...:

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

and assume both id and name columns to be unique. What will the above statements return?

Closely related, how could I find the ID of the existing row that causes a conflict?

PS: Yes, I could easily just try it out. In fact, that's what I did (see the answer). It's easy to make a wrong guess, hard to remember the answer and expensive to reproduce the experiment, so I thought this question would be useful to others.

Answer 1

The returning clause always returns the information that was written to the table (i.e. inserted or updated). If no write occurred, no row will be returned.

As a consequence, if you want to obtain the id of the existing row that causes a conflict, you cannot use on conflict do nothing. Instead, you could use on conflict (...) do update with a dummy update to provoke a write that doesn't actually modify the contents of the existing row, like so:

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;

Note however that this artificial write may still have undesirable side-effects, especially if triggers are in place. You'll also generate new tuples even if the data doesn't change.

Experimental proof:

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