On Fri, Aug 6, 2010 at 3:53 PM, Simon Riggs
<simon@2ndquadrant.com> wrote:
On Fri, 2010-08-06 at 10:28 +0300, Heikki Linnakangas wrote:
> On 06/08/10 10:12, Simon Riggs wrote:
> > 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.
>
> I see neither DO NOTHING or RAISE ERROR in the documentation of DB2,
> Oracle, or MSSQL server.
Agreed, Oracle and MSSQL server does not have these.
However, DB2 very clearly does have these features
* SIGNAL which raises an error and can be used in place of any action,
at any point in sequence of WHEN clauses. DB2 already supports SIGNAL as
part of SQL/PSM, which we do not, so RAISE ERROR was the nearest
equivalent command for PostgreSQL.
* ELSE IGNORE which does same thing as DO NOTHING, except it must always
be last statement in a sequence of WHEN clauses. DO NOTHING is already a
phrase with exactly this meaning in PostgreSQL, so I suggest that.
So, we need to add both DO NOTHING and RAISE ERROR actions in the MERGE command now !? What will RAISE ERROR do? To stop the whole MERGE command OR, just throw an error notice for the row and move on.