Re: Partial update on an postgres upsert violates constraint

On 11/19/2016 11:33 AM, Kim Rose Carlsen wrote:
>> 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?
>
> From the manual
> https://www.postgresql.org/docs/9.5/static/sql-insert.html
>
> "
> conflict_action
> conflict_action specifies an alternative ON CONFLICT action. It can be
> either DO NOTHING, or a DO UPDATE clause specifying the exact details of
> the UPDATE action to be performed in case of a conflict. The SET and
> WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row
> using the table's name (or an alias), and to rows proposed for insertion
> using the special excluded table. SELECT privilege is required on any
> column in the target table where corresponding excluded columns are read.
> "
>


Oops, my mistake. I should have spent more time on the examples.

Changing the function to;

CREATE OR REPLACE FUNCTION public.upsert_job(job jsonb)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
INSERT INTO jobs AS origin VALUES(
    (job->>'id')::INTEGER,
    COALESCE(job->>'employee_name'::TEXT, 'test_name'),
    COALESCE(job->>'address'::TEXT, 'test_address'),
    job->>'phone_number'::TEXT
) ON CONFLICT (id) DO UPDATE SET
    employee_name = COALESCE(EXCLUDED.employee_name, origin.employee_name),
    address = COALESCE(EXCLUDED.address, origin.address),
    phone_number = COALESCE(EXCLUDED.phone_number, origin.phone_number);
END;
$function$
;

makes it work. So looks like constraints are checked before you get to the ON CONFLICT section.


--
Adrian Klaver
adrian.klaver@aklaver.com