Re: MERGE vs REPLACE

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.

BTW, my reading of the MERGE examples given earlier is that there no
notes in there at all about guarenteeing concurrency. None of the
documentation says that using MERGE will avoid duplicate key errors if
someone else does the same thing concurrently. It seems more like a
performence hack to avoid scanning the table twice.

Basically, you could implement this by taking the USING clause, do a
left outer join with the merge table and for the blank rows fill in a
CTID for insert and instead of NULLs the values of the INSERT portion.

Which is kind of a bummer for the people who want to do the "insert
zero if not there else add 1" thing a lot and expecting this to solve
the concurrency for them.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.