Re: MERGE vs REPLACE

On Wed, Nov 16, 2005 at 04:51:07PM -0500, Tom Lane wrote:
> daveg <daveg(at)sonic(dot)net> writes:
> > I agree, but would like to relax the primary key requirement to simply
> > a unique index. I can see use cases for unique so long as not null keys,
> > so it would be nice if the MERGE operation would work for these. As nulls
> > are not "equal" anyway this doesn't seem to do too much violence to the
> > semantics.
>
> But a "unique" key doesn't guarantee that there's only one matching row,
> so ISTM you're right back to needing a predicate lock if you do that.

But there is no need to guarentee anything. As the spec says, if the
join of the table with the other clauses matches a row in the table
more than once, raise a cardinality exception. If someone creates a
join that matches more than once the whole statement fails. But you can
work that out at runtime. If the user specifies NOT NULL in the join
condition then it can work and there no reason to forbid that.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.