On Mon, Sep 29, 2014 at 2:14 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> 1. SQL Standard MERGE (or a subset)
> 2. MySQL Compatible syntax
> 3. Something completely different
>
> If we go for (3), I would like to see a long and detailed explanation
> of what is wrong with (1) and (2) before we do (3). That needs to be
> clear, detailed, well researched, correct and agreed. Otherwise when
> we release such a feature, people will ask, why did you do that? And
> yet nobody will remember.
My syntax is inspired by the MySQL one, with some influence from
SQLite (SQLite have an ON CONFLICT REPLACE). I don't want to copy
MySQL's use of VALUES() in the UPDATE targetlist - I spell the same
concept as CONFLICTING(). I guess that otherwise they'd have to make
the VALUES()/CONFLICTING() expression a whole new fully reserved
keyword, and preferred not to. Also, MySQL bizarrely omits the "SET"
keyword within ON DUPLICATE KEY UPDATE. So I haven't copied it exactly
on aesthetic grounds. I think that the actual reason for the latter
wart (the SET omission) is that MySQL found it easier to write the
grammar that way. Consider what we do here to make SET in an UPDATE
work, despite the fact that it's a valid column name:
https://github.com/postgres/postgres/blob/REL9_4_STABLE/src/backend/parser/gram.y#L10141
So I wanted to suggest something similar but not identical to the
MySQL syntax, with a bit more flexibility/safety. I thought that I
could do so without emulating their warts.
As I've mentioned, it isn't the MERGE syntax because that is quite a
different thing. There is a place for it, but it's not strategically
important in the same way as upsert is.
--
Peter Geoghegan