Re: Making joins involving ctid work for the benefit of UPSERT

On Fri, Jul 18, 2014 at 11:06 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> I don't see why you'd need such a node at all if we had a fully builtin
> UPSERT. The whole stuff with ON CONFLICT SELECT FOR UPDATE and then
> UPDATE ... FROM c CONFLICTS is too complicated and exposes stuff that
> barely anybody will understand, let alone use correctly in queries they
> write themselves.

I accept that there will be a need for certain restrictions. Most
obviously, if you update the target table referencing a CTE like this,
not using the special CONFLICTS clause in the UPDATE (or DELETE) is an
error. And as I mentioned, you may only join the projected duplicates
to the UPDATE ModifyTable - an attempt to join any more relations is
an error. In short, this *is* a fully built-in upsert.

--
Peter Geoghegan