Re: how to return data from insert into ... on conflict ... returning ... into

On Tue, Jun 20, 2023 at 1:26 PM Les <nagylzs(at)gmail(dot)com> wrote:
>
> https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
>
> and the only possible actions are "do nothing" and "do update". The "do nothing" does not update the record, that is clear. But it also does not return any data, and that was not obvious to me. Why can't it return the data from the conflicted record? After the instruction is completed, "GET DIAGNOSTICS" could still be used to check the number of updates.

Would it be fair to summarize that you'd like a feature where, upon
conflict, the command behaves as SELECT, and returns row that caused
the conflict?

The docs say: "If the INSERT command contains a RETURNING clause, the
result will be similar to that of a SELECT statement containing the
columns and values defined in the RETURNING list, computed over the
row(s) inserted or updated by the command."

So in your desired behaviour, the RETURNING list would be computed
over the rows that cause the conflict.

> I understand that changing this behaviour may break backward compatibility. Would it be possible to introduce a "DO RETURN" clause that returns the data, even when there was a conflict?

If this behaviour is introduced with a new syntax, then there won't be
any fears of breaking backwards compatibility. I think DO SELECT as
the new syntax will make the intent clear. Taking one of your queries
as an example, after the new syntax it will look as follows:

insert into tbl(d) values ('1') on conflict(h) DO SELECT returning id into aid;

Best regards,
Gurjeet
http://Gurje.et