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

Hi Tom,

That may be "the design" and it may be a fine design for insert, but
it's not a great design for upsert, which was sort of my point.

An update statement does not require all NOT NULL columns to be
specified so neither should an upsert require all NOT NULL columns to be
specified in the case where the update will run.

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...?

Thanks for all the hard work. I've only been using Postgres for a few
months, but thus far it's been solid.

-- Reyes

On 9/27/2016 3:06 PM, Tom Lane wrote:
> reyes(dot)r(dot)ponce(at)gmail(dot)com writes:
>> ERROR: null value in column "col1" violates not-null constraint
>> DETAIL: Failing row contains (1, null, 5, 2016-09-27 17:32:51.054896+00,
>> pl_mstr_usr, 2016-09-27 17:32:51.054896+00, pl_mstr_usr).
>> CONTEXT: SQL statement "INSERT INTO public.MyTable(
>> MY_ID, COL1, COL2, CRETN_TS, CRETN_USER_ID, UPDT_TS,
>> UPDT_USER_ID)
>> VALUES ($1, $2, $3, NOW(), current_user, NOW(), current_user)
>> ON CONFLICT(MY_ID)
>> DO UPDATE SET UPDT_TS = NOW(), UPDT_USER_ID = current_user, COL2 = $3"
>> PL/pgSQL function upsert_mytable(integer,integer,integer) line 46 at
>> EXECUTE
> This test case seems rather overcomplicated, but AFAICS you are
> complaining because the NOT NULL constraint is checked before uniqueness
> is checked. Sorry, that is not a bug, that is by design.
>
> regards, tom lane
>