PostgreSQL: the good, the bad, and the ugly

How is that worse than having the same syntax, but doing something different? If the behaviour is different, there's no way in keeping compatibility at the SQL level. IMSNHO it's exactly the opposite - it's way better to visibly break the application, than to accept the SQL and do something subtly different.

FWIW, we looked closely at adopting an existing syntax and didn't take adding a new one lightly.

MySQL's ON DUPLICATE KEY UPDATE is the most popular form. In addition to being a little bit ugly (missing SET, etc), though, it offers no way to specify which unique constraint or index should be considered the key. MySQL's documentation says that behaviour in the presence of multiple unique indexes is undefined, which isn't something we're willing to accept in PostgreSQL. Additionally, the MySQL form offers no way to access both the old tuple and the proposed tuple in case of conflict.

Lots of people ask why we didn't just use MERGE, but that's already been explained in detail upthread. Basically it isn't upsert, it's an OLAP statement with largely undefined concurrency semantics, and if we try to turn it into an upsert we'll break the real use cases for MERGE.

The PostgreSQL syntax is fairly close to MySQL's, and we'd really love the MySQL folks to adopt it since it'd be a fairly simple extension of what they currently have.

What we really need here is for the SQL standard to recognise that MERGE does not serve the purpose of OLTP upsert, and define a standard UPSERT, either via an actual UPSERT statement, or via extensions to INSERT. Naturally I'd prefer to see them adopt the PostgreSQL syntax, but we're more likely to land up with something baroque and verbose with at least two unnecessary new keywords used in confusing contexts and at least one bizarre pseudo-function that requires new parser logic...