Re: MERGE SQL Statement for PG11

via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx&cv=9.8.79&pv=10.12.6&source=email_footer_2]
On Fri, Oct 27, 2017 at 2:42 PM, Peter Geoghegan <pg@bowt.ie> wrote:
On Fri, Oct 27, 2017 at 2:13 PM, srielau <serge@rielau.com> wrote:
> While the standard may not require a unique index for the ON clause I have
> never seen a MERGE statement that did not have this property. So IMHO this
> is a reasonable restrictions.

The Oracle docs on MERGE say nothing about unique indexes or
constraints. They don't even mention them in passing. They do say
"This statement is a convenient way to combine multiple operations. It
lets you avoid multiple INSERT, UPDATE, and DELETE DML statements."

SQL Server's MERGE docs do mention unique indexes, but only in
passing, saying something about unique violations, and that unique
violations *cannot* be suppressed in MERGE, even though that's
possible with other DML statements (with something called
IGNORE_DUP_KEY).

What other systems *do* have this restriction? I've never seen one that did. Not clear what you are leading up to here. When I did MERGE in DB2 there was also no limitation: " Each row in the target can only be operated on once. A row in the target can only be identified as MATCHED with one row in the result table of the table-reference” What there was however was a significant amount of code I had to write and test to enforce the above second sentence. IIRC it involved, in the absence of a proof that the join could not expand, adding a row_number() over() AS rn over the target leg of the join and then a row_number() over(partition by rn) > 1 THEN RAISE_ERROR() to catch violators. Maybe in PG there is a trivial way to detect an expanding join and block it at runtime.
So the whole point I’m trying to make is that I haven’t seen the need for the extra work I had to do once the feature appeared in the wild.
Cheers Serge