Re: MERGE vs REPLACE

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.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