On Saturday 12 November 2005 04:06, Matteo Beccati wrote: > Tom Lane wrote: > > Peter Eisentraut <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match