Re: INSERT ... ON CONFLICT () SELECT

On Sun, Jun 18, 2017 at 9:21 PM, Peter Geoghegan <pg(at)bowt(dot)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 ...

1. Rows that are in conflict are made available to the RETURNING clause.
In other words, it is like an idempotent "ON CONFLICT DO UPDATE".
2. 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.
3. 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. :)

Best regards,
Matt