On Tue, Jun 20, 2023 at 1:26 PM Les <nagylzs@xxxxxxxxx> 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
