Re: MERGE vs REPLACE

On Saturday 12 November 2005 04:06, Matteo Beccati wrote:
> 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.
>
>

Wow, that seems ugly.... maybe there's a reason for it, but I'm not sure we
could deviate from my$ql's behavior on this even if we wanted... they are the
"standard" here.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL