Re: BUG #14343: UPSERT (ON CONFLICT) doesn't check ON CONFLICT constraint first

Hi Peter,

In my case I'm writing a stored procedure that will insert or update based
on whether the row already exists. Some things calling it will be
interactive and some will be batch processes. Sometimes all the parameters
will be not null and sometimes some of the parameters will be null. The
stored procedure should do the right thing (insert or update) as best it
can or throw an error. Regardless, of this specific design, the following
are still true:

1) Upsert is becoming a common feature (probably because it matches well
with the definition of REST PUT and POST functionality).

2) The current INSERT... ON CONFLICT... DO UPDATE... doesn't handle the
scenarios stated in my initial email, which upsert functionality can
usually handle.

3) Upsert can be done with CTEs which can handle those scenarios but has
potential race conditions.

which is why I am asking about this (i.e. if the insert version of upsert
can't handle these scenarios, maybe we need an upsert based on update).

On Oct 1, 2016 8:21 AM, "Peter Geoghegan" <pg(at)heroku(dot)com> wrote:

> On Fri, Sep 30, 2016 at 8:19 PM, Reyes Ponce <reyes(dot)r(dot)ponce(at)gmail(dot)com>
> wrote:
> > Any chance you guys will do a
> >
> > UPDATE ... ON MISSING... DO INSERT...
> >
> > version as I expect in that case it would be implemented closer to the
> > functionality you get implementing upsert with a CTE (and how upsert in
> > most NoSql DB works (i.e. doesn't impose more restrictions than update
> > in the update case)) which would cover far more use cases than the
> > current design of INSERT... ON CONFLICT... DO UPDATE...?
>
> I don't think that that makes sense. If you know ahead of time that
> the INSERT path will definitely throw an error, you're either relying
> on that path never being taken, in which case you should just use a
> plain UPDATE, or you have a bug in your application code, in which
> case you should be glad to have it surfaced sooner rather than later.
>
> --
> Peter Geoghegan
>