Re: INSERT ... ON CONFLICT syntax issues

* Peter Geoghegan (pg(at)heroku(dot)com) wrote:
> On Tue, Apr 28, 2015 at 9:42 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > I agree with that, but how are NEW and OLD ambiguous? NEW is clearly
> > the tuple being added, while OLD is clearly the existing tuple.
>
> Yes, but EXCLUDED is neither the tuple being added, nor is it the new
> tuple. It's something else entirely.

I don't see that, it's exactly the tuple attempting to be inserted. I
agree that it might not be the tuple originally in the INSERT statement
due to before triggers, but there isn't an alias anywhere for that.

Now, in 99% of cases there aren't going to be before triggers so I'm not
particularly worried about that distinction, nor do I think we need to
provide an alias for the tuple from the INSERT piece of the clause, but
to say that EXCLUDED isn't the tuple being added doesn't make any sense
to me, based on how I read the documentation proposed here:

http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html

I'll further point out that the documentation doesn't bother to make the
before-trigger distinction always either:

Note that an EXCLUDED expression is used to reference values originally
proposed for insertion:

Perhaps I've missed something, but that seems to go along with the
notion that EXCLUDED references the tuple that we're attempting to add
through the INSERT, and that's certainly what I'd consider NEW to be
also.

I do think there's a real issue with using OLD/NEW because of their
usage in triggers, and I don't see any good solution to that issue. :(

Thanks,

Stephen