Re: INSERT ... ON CONFLICT syntax issues

On Tue, Apr 28, 2015 at 9:58 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * 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.
>

​So? I see this as a prime case for choosing practicality/functionality
over precision.

​If I was to pick 2 words I would probably pick "PROPOSED" and "EXISTING".

But, the syntax is already verbose and being able to use a three-letter​
reference has its own appeal.

> 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

​This example exemplifies the poorness of the proposed wording, IMO:

​

​[...] ​
SET dname = EXCLUDED.dname || ' (formerly ' || TARGET.dname || ')'

​NEW.dname || '(formerly ' || OLD.dname || ')' reads perfectly well.

Yes, this is an isolated example...​but am I missing the fact that there is
a third tuple that needs to be referenced?

If there are only two the choices of NEW and OLD seem to be both easily
learned and readable.

​David J.
​