MERGE Support (SQL2003) - Mailing list pgsql-hackers

Greetings,
 As mentioned in the PostgreSQL Weekly News, SQL2003 has been approved. Looking at one of the recent drafts it appears
thatMERGE has been added to the spec.  MERGE is described as "Conditionally update rows of a table, or insert new rows
intoa table, or both."  Support for this has been asked for in the past and if anything demand for this has increased.
Isanyone working on this?  Is there any status on it?
 
 Additionally, from the draft I'm reading the SQL2003 standard makes MERGE very capable but without obvious defaults
forthe simple case. While I believe support for the standard should be paramount it would make a great deal of sense to
allowsimple operations without unnecessary complexity.
 
 For example: Table T1 exists with columns a, b, c where a, b is the primary key.  T1 contains a single tuple '1, 2,
3'. I would now like to either update or insert the primary key '1, 2' with the value '4'.
 
 From what I understand of the specification this would be done by:

a) insert into T2 values (1,2,4); merge into T1       USING T2ON T1.a=T2.a and T1.b=T2.b   WHEN MATCHED THEN
UPDATESET T1.c = T2.c       ON T1.a=T2.a and T1.b=T2.b   WHEN NOT MATCHED THEN        INSERT (a,b,c) VALUES
(T2.a,T2.b,T2.c);
 This requires an addtional table (T2).  There may be a better way around this but I know that PostgreSQL already
allowsselect statements without a from clause, so this could be simplified to:
 

b) merge into T1       USING (select 1 as a, 2 as b, 4 as c) as T2ON T1.a=T2.a and T1.b=T2.b   WHEN MATCHED THEN
UPDATESET T1.c = T2.c       ON T1.a=T2.a and T1.b=T2.b   WHEN NOT MATCHED THEN        INSERT (a,b,c) VALUES
(T2.a,T2.b,T2.c);
 Still pretty long-winded for what most would consider a relatively simple request.  My goal would be the ability to
haveimplied ON/WHEN clauses and USING VALUES, like so:
 

c) merge into T1 USING VALUES (1,2,4);
 The ON clauses are implied primary key matches.  The WHEN clause for MATCHED is then to UPDATE SET all columns which
arenot part of the primary key.  The WHEN clause for NOT MATCHED is to INSERT the row. Clearly this last usage is what
Iwould prefer for this case.  It also parallels the 'replace into' which MySQL has which would make migration from
MySQLto PostgreSQL much easier for programs and users. Please let me know if there's something I'm missing in the
specificationthat would allow for a simple case similar to what I've illustrated, either with MERGE or without.  Of
course,the expectation is that MERGE wouldn't be able to fail because of another instance adding a row with the same
primarykey.
 
 I plan to forward this suggestion on to the SQL committee as well, though I don't know what kind of response, if any,
I'llget.  Feel free to address the standard MERGE support seperately from my suggestion.  I have need for both and so
bothare of interest and do not have to come at the same time.
 
     Many thanks,
    Stephen