On Sun, Jun 18, 2017 at 9:21 PM, Peter Geoghegan <pg@bowt.ie> wrote:
Returning rows with duplicate values seems rather unorthodox.
Ok, then option 2 it is.
In summary, this is what I am going to (attempt to) implement for the new syntax:
INSERT ...
ON CONFLICT (...) DO SELECT
RETURNING ...
Rows that are in conflict are made available to the RETURNING clause. In other words, it is like an idempotent "ON CONFLICT DO UPDATE".
Similarly, insertion sets that would cause the error "ON CONFLICT DO UPDATE command cannot affect row a second time" if it were an "ON CONFLICT DO UPDATE" statement will also cause a similar error for "ON CONFLICT DO SELECT". This will prevent duplicate rows from being returned.
Like an "ON CONFLICT DO UPDATE", the returned rows cannot be changed by another part of the wCTE, even if no actual insertions occurred.
Unless I have missed anything, I think all other issues have been adequately addressed. Since there are no red lights, I shall proceed. :)
