Re: MERGE vs REPLACE

Tom Lane wrote:
> Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:
> > OK, in this case I don't care about either MERGE or REPLACE, but for an
> > UPSERT which does the locking :-)
>
> This is exactly the point --- pretty much nobody has come to us and
> asked for a feature that does what Peter and Martijn say MERGE does.
> (I haven't bothered to look at the 2003 spec, I'm assuming they read it
> correctly.) What we *have* been asked for, over and over, is an
> insert-or-update feature that's not so tedious and inefficient as the
> savepoint-insert-rollback-update kluge. That's what we ought to be
> concentrating on providing.

I am confused over the various options. I have heard these syntaxes:

SQL2003 MERGE
MySQL REPLACE
http://dev.mysql.com/doc/refman/5.1/en/replace.html
MySQL INSERT VIOLATION ...
UPSERT

So it seems MERGE does not have the use-case we most need, though it can
be bent to do it. (Given their MATCH syntax, it doesn't seem there is
any logic that it tries INSERT first).

Looking at the MySQL URL above, REPLACE has three possible syntaxes with
normal (DELETE), SET (UPDATE), and SELECT. Is this the direction we
need to go? I don't like INSERT ... VIOLATION because I would like a
new keyword for this. Is UPSERT the same as REPLACE? Should we use
UPSERT instead?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073