Re: MERGE vs REPLACE - Mailing list pgsql-hackers

On Sun, 2005-11-13 at 23:56 +0100, Martijn van Oosterhout wrote:
> On Sun, Nov 13, 2005 at 11:32:47PM +0100, 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) ...
> > But I am not sure if this is what you want.
> 
> Well, the obvious extension to this is that the extire USING clause is
> in fact optional:
> 
> MERGE INTO tablename ON id = 1 ...
> 
> Which starts looking a lot simpler.
> 

...choosing a place to jump in is a little hard in this thread...so I'll
jump in with some general info and thoughts on topics so far:

MERGE is useful both for OLTP systems and for Data Warehousing, where it
is sometimes known as the UPSERT. The MERGE statement in SQL:2003
requires a target table and a table statement. I don't see anything in
that to always require two separate tables - this is just the same as a
self-referencing INSERT SELECT statement. The USING clause is also a
compulsory part of SQL:2003.

One of the more interesting ways to use MERGE is with Oracle external
tables. The same idea for us would be to have MERGE become a variant of
the PostgreSQL COPY FROM command. That would be very cool.

The above is the reason why MERGE doesn't seem to provide for external
data being passed, as does INSERT or MySQL REPLACE.

Neither DB2 or Oracle perform predicate locking. DB2 is more cautious,
and some would say more efficient, thats all. PostgreSQL's locking
features are just fine for pragmatic implementation of MERGE, AFAICS.
Where there is doubt, we should fall back to table locking just like the
rest of the world, IMHO. Making this work with partitioning will be hard
enough without overusing the predicate solving logic.

The UPSERT concept is also supported by Teradata, who simply append an
ELSE INSERT clause onto the standard UPDATE syntax. MySQL REPLACE seems
to me to be a fairly small subset of MERGE functionality and we ought to
be able to offer that functionality as a side branch of the main work.

I've been looking at ways of doing INSERT then UPDATE, but it doesn't
seem very easy to avoid unique index violations in that case. So doing
the UPDATE first then INSERTs later seems like the way to go.

Best Regards, Simon Riggs