Re: Partial update on an postgres upsert violates constraint

On 11/17/2016 10:13 PM, Andreas Terrius wrote:
> Hi,
> Basically I wanted to do a partial update inside pg (9.5), but it seems
> that a partial update fails when not all of constraint is fulfilled
> (such as the not null constraint)
>
> Below are the sql queries I used,
>
> |CREATETABLEjobs (id integer PRIMARYKEY,employee_name TEXT
> NOTNULL,address TEXT NOTNULL,phone_number TEXT );CREATEORREPLACE
> FUNCTIONupsert_job(job JSONB)RETURNS VOID AS$$BEGININSERTINTOjobs
> ASorigin
> VALUES((job->>'id')::INTEGER,job->>'employee_name'::TEXT,job->>'address'::TEXT,job->>'phone_number'::TEXT
> )ONCONFLICT (id)DO UPDATESETemployee_name
> =COALESCE(EXCLUDED.employee_name,origin.employee_name),address
> =COALESCE(EXCLUDED.address,origin.address),phone_number
> =COALESCE(EXCLUDED.phone_number,origin.phone_number);END;$$LANGUAGE
> PLPGSQL SECURITY DEFINER;--Full insert (OK)SELECTupsert_job('{"id" : 1,
> "employee_name" : "AAA", "address" : "City, x street no.y",
> "phone_number" : "123456789"}'::jsonb);--Partial update that fulfills
> constraint (Ok)SELECTupsert_job('{"id" : 1, "employee_name" : "BBB",
> "address" : "City, x street no.y"}'::jsonb);--Partial update that
> doesn't fulfill constraint (FAILS)SELECTupsert_job('{"id" : 1,
> "phone_number" : "12345"}'::jsonb);--ERROR: null value in column
> "employee_name" violates not-null constraint--DETAIL: Failing row
> contains (1, null, null, 12345).|
>
> I also tried explicitly stating the columns that I wanted to insert, and
> it also fails. How do I go around doing this ?

AFAIK, EXCLUDED is only available in a trigger function:

https://www.postgresql.org/docs/9.5/static/trigger-definition.html

You are using EXCLUDED in a regular function so it would not be found.

Can you also show the failure for your alternate method?

>
> Thank you


--
Adrian Klaver
adrian.klaver@aklaver.com