Re: INSERT ... ON CONFLICT () SELECT

On Sun, Jun 18, 2017 at 4:33 AM, Matt Pulver <mpulver(at)unitytechgroup(dot)com> wrote:
> This would be nearly identical to the existing error message that is
> produced when running:
>
> INSERT INTO example (name) VALUES ('foo'), ('foo')
> ON CONFLICT (name) DO UPDATE SET value=1
> RETURNING *
>
>
> which gives the error message:
> "ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
> HINT: Ensure that no rows proposed for insertion within the same command
> have duplicate constrained values."

FWIW, the fact that ON CONFLICT DO UPDATE will never update a row that
it itself inserted has proved valuable. For example, transition
tables, which appeared within statement triggers, have no need to
consider the case where an inserted tuple appears for the INSERT
statement case, as well as the UPDATE statement case (in another
form).

> Technically, an error doesn't need to be produced for the above "ON CONFLICT
> DO SELECT" statement - I think it is still perfectly well-defined to return
> duplicate rows as in option 1.

Returning rows with duplicate values seems rather unorthodox.

--
Peter Geoghegan