Re: MERGE vs REPLACE

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> It seems to me that it has always been implicitly assumed around here
>> that the MERGE command would be a substitute for a MySQL-like REPLACE
>> functionality. After rereading the spec it seems that this is not the
>> case. MERGE always operates on two different tables, which REPLACE
>> doesn't do.
>
> Normally I'd plump for following the standard ... but AFAIR, we have had
> bucketloads of requests for REPLACE functionality, and not one request
> for spec-compatible MERGE. If, as it appears, full-spec MERGE is also a
> whole lot harder and slower than REPLACE, it seems that we could do
> worse than to concentrate on doing REPLACE for now. (We can always come
> back to MERGE some other day.)

I would also like to add that MySQL's REPLACE is not exactly an INSERT
OR UPDATE, rather and INSERT OR (DELETE then INSERT): I mean that the
fields not specified in the query are set to their defaults:

i.e.

CREATE TABLE t (a int PRIMARY KEY, b int, c int);

INSERT INTO t (a, b, c) VALUES (1, 1, 2);

SELECT * FROM t;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 2 |
+---+------+------+

REPLACE INTO t (a, b) VALUES (1, 1);

SELECT * FROM t;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | NULL |
+---+------+------+

I wanted to point it out this because people are commonly mistaking this.

Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com