On Fri, 2010-08-06 at 09:39 +0800, Boxuan Zhai wrote: > Besides, (I mean no offense, but) can this method really avoid losing > row?
Not as you just specified, no. You need *both* actions of RAISE ERROR and DO NOTHING, or you may as well have neither. (1) Natural style allows missing rows if you are not careful - and also allows missing rows in future when COL is allowed to take value 'C', which may not have been originally considered when SQL first written WHEN NOT MATCHED AND COL = 'A' INSERT... WHEN NOT MATCHED AND COL = 'B' INSERT... (2) Shows code style required to explicitly avoid missing rows WHEN NOT MATCHED AND COL = 'A' INSERT... WHEN NOT MATCHED AND COL = 'B' INSERT... WHEN NOT MATCHED RAISE ERROR (3) More complex example, with explicit DO NOTHING, showing how it can provide well structured code WHEN NOT MATCHED AND COL = 'A' DO NOTHING WHEN NOT MATCHED AND COL = 'B' INSERT... WHEN NOT MATCHED RAISE ERROR So DO NOTHING is the default and implies silently ignoring rows. RAISE ERROR is the opposite. Coding for those seems very easy, its just a question of "should we do it?". DB2 has it; SQL:2008 does not. But then SQL:2008 followed the DB2 introduction of AND clauses, and SQL:2011 has so far followed the DB2 introduction of DELETE action also. Given that Peter is now attending SQL Standards meetings, I would suggest we leave out my suggestion above, for now. We have time to raise this at standards meetings and influence the outcome and then follow later. There is a workaround: WHEN NOT MATCHED AND COL = 'A' DO NOTHING WHEN NOT MATCHED AND COL = 'B' INSERT... WHEN NOT MATCHED AND TRUE INSERT INTO ERROR_TABLE (errortext); where ERROR_TABLE has an INSERT trigger which throws an ERROR with given text. SQL:2011 makes no mention of how MERGE should react to statement level triggers. MERGE is not a trigger action even. Given considerable confusion in this area, IMHO we should just say the MERGE does not call statement triggers at all, of any kind. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
