Re: Best practices: MERGE

On Mon, 2005-03-07 at 19:34 -0800, David Fetter wrote:

> Although the SQL:2003 command MERGE has not yet been implemented in
> PostgreSQL, I'm guessing that there are best practices for how to
> implement the MERGE functionality.
>
> To recap, MERGE means (roughly) INSERT the tuple if no tuple matches
> certain criteria, otherwise UPDATE using similar criteria.

Don't understand that way round...

I thought the logic was:
UPDATE WHERE ..... (locate row)
IF NOT FOUND THEN
INSERT (new row)

You can create a procedure to do that, but MERGE would work better.

ISTM that would require writing some new code that was a mix of
heap_update and heap_insert logic for the low level stuff would be
required. The existing heap_update code is most similar, since the logic
is roughly

UPDATE WHERE.... (locate row)
IF FOUND THEN
INSERT (new row version)

though with various changes to row visibility stuff.

One might aim to do this in two stages:
1. initially support a single row upsert such as MySQL's REPLACE command
2. a full implementation of MERGE that used set logic as per the spec

...

Best Regards, Simon Riggs