Re: MERGE vs REPLACE - Mailing list pgsql-hackers

Just to summarize, MySQL REPLACE is INSERT or DELETE/INSERT, while they
have a SET clauses that allows UPDATE, and INSERT has a ON DUPLICATE KEY
UPDATE clause too.

I think the INSERT ...  ON DUPLICATE KEY is undesirable because this
functionality should have a new keyword in the first position, e.g
MERGE, and I think the REPLACE is out because most people feel that the
DELETE/INSERT functionality is near-useless if we can give users the
INSERT/UPDATE functionality of MERGE.

I think even if we have to restrict MERGE to requiring a unique index,
it is better to go that way than to drag REPLACE into our syntax.  MERGE
can be extended over time, while REPLACE has a non-optimal initial
behavior.  The idea that MERGE can use a constant list (not requiring a
second table) makes it a valid replacement for REPLACE, and other
database support for MERGE reinforces this.

---------------------------------------------------------------------------

pmagnoli@systemevolution.it wrote:
> I think you translated it correctly, MySQL has another way of specifying this
> which is "INSERT ... ON DUPLICATE KEY UPDATE ..."
> (http://dev.mysql.com/doc/refman/5.0/en/insert.html)
> Regards
> 
> Paolo
> 
> Jochem van Dieten <jochemd@gmail.com> ha scritto
> 
> > On 11/13/05, Petr Jelinek wrote:
> > >
> > > I am really not db expert and I don't have copy of sql standard but you
> > > don't need to use 2 tables I think - USING part can also be subquery
> > > (some SELECT) and if I am right then you could simulate what REPLACE
> > > does because in PostgreSQL you are not forced to specify FROM clause in
> > > SELECT. So you could in theory do
> > > MERGE INTO tablename USING (SELECT 1 AS myid) ON (tablename.id = myid)
> ...
> 
> > I think the MySQL statement:
> > REPLACE INTO table (pk, col1, col2, col3) VALUES (2, '0000-00-00', NULL,
> 3)
> 
> > would translate into the following MERGE statement:
> > MERGE INTO table target
> > USING (2 as pknew , NULL as col1new, NULL as col2new, 3 as col3new) source
> > ON target.pknew = source.pk
> > WHEN MATCHED THEN UPDATE SET col1 = col1new, col2 = col2new, col3 =
> col3new
> > WHEN NOT MATCHED THEN INSERT (pk, col1, col2, col3) VALUES (pknew,
> > col1new, col2new, col3new)
> 
> > It might not be the most elegant solution, but I don't see why it won't
> work.
> 
> > Jochem
> 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> 
> >                http://archives.postgresql.org
> > 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

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