Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Wed, Oct 8, 2014 at 10:49 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Do you really expect me to do major work on some aspect of the syntax
>> like that, given, as you say, that nobody explicitly agreed with me
>> (and only you disagreed with me)? The only remark I heard on that was
>> from you (you'd prefer to use NEW.* and OLD.*).
>> But you spent much
>> more time talking about getting something MERGE-like, which
>> NEW.*/OLD.* clearly isn't.
>
> Yes, it is. Look at the AS clause.

You can alias each of the two tables being joined. But I only have one
table, and no join. When you referred to NEW.* and OLD.*, you clearly
were making a comparison with trigger WHEN clauses, and not MERGE
(which is a comparison I made myself, although for more technical
reasons). It hardly matters, though.

>> CONFLICTING() is very close (identical?) to MySQL's use of "ON
>> DUPLICATE KEY UPDATE val = VALUES(val)". I'm happy to discuss that,
>> but it's news to me that people take particular issue with it.
>
> 3 people have asked you questions or commented about the use of
> CONFLICTING() while I've been watching.

Lots of people have asked me lots of questions. Again, as I said, I
wasn't aware that CONFLICTING() was a particular point of contention.
Please be more specific.

> It's clearly a non-standard
> mechanism and not inline with other Postgres usage.

What would be "inline with other Postgres usage"? I don't think you've
been clear on what you think is a better alternative.

I felt a function-like expression was appropriate because the user
refers to different tuples of the target table. It isn't like a join.
Plus it's similar to the MySQL thing, but doesn't misuse VALUES() as a
function-like thing.

> If there is disagreement, publishing the summary of changes you plan
> to make in your next version will help highlight that.

I think I've done a pretty good job of collecting and collating the
opinions of others, fwiw.
--
Peter Geoghegan