Thread: 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

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

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> ... So looks like constraints are checked before you get to the ON CONFLICT section.

Right.  ON CONFLICT is a means for dealing with duplicate-key errors in
the specified (or inferred) unique index.  It is *not* an all-purpose
error catcher.  In the case at hand, the given INSERT request fails due
to not-null constraints that are unrelated to what the ON CONFLICT clause
tests for.

            regards, tom lane

On 11/21/2016 02:32 AM, Andreas Terrius wrote:
> Is there any way to check whether the row already exists before checking
> constraints ? I still want it to fail if it turns out to be a new row
> (which would violate the not null constraint), but updates the row if it
> already exists.
>
> Since if that is not possible, I would need to do a query to determine
> whether the row exists in the database which kinda eliminates the use of
> upsert. (in this case, partial upsert).

Before UPSERT appeared in 9.5, folks came up of with alternate methods
of doing this. I would suggest searching on:

postgres upsert cte


You might be able to modify the examples to get what you want.

>
>
>
> On Sun, Nov 20, 2016 at 3:57 AM, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     Adrian Klaver <adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>> writes:
>     > ... So looks like constraints are checked before you get to the ON
>     CONFLICT section.
>
>     Right.  ON CONFLICT is a means for dealing with duplicate-key errors in
>     the specified (or inferred) unique index.  It is *not* an all-purpose
>     error catcher.  In the case at hand, the given INSERT request fails due
>     to not-null constraints that are unrelated to what the ON CONFLICT
>     clause
>     tests for.
>
>                             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/21/2016 2:32 AM, Andreas Terrius wrote:
> Is there any way to check whether the row already exists before
> checking constraints ? I still want it to fail if it turns out to be a
> new row (which would violate the not null constraint), but updates the
> row if it already exists.

just do an update.  if the row doesn't exist, it will fail, you then
rollback the transaction or savepoint.

> Since if that is not possible, I would need to do a query to determine
> whether the row exists in the database which kinda eliminates the use
> of upsert. (in this case, partial upsert).

in general, anything that relies on those sorts of checks will fail
under concurrent loads.



--
john r pierce, recycling bits in santa cruz

Andreas Terrius <gotenwinz99@gmail.com> writes:
> Is there any way to check whether the row already exists before checking
> constraints ? I still want it to fail if it turns out to be a new row
> (which would violate the not null constraint), but updates the row if it
> already exists.

I'm not really sure why you expect this to be able to work.  The data
you are supplying is insufficient to satisfy the INSERT case, so why do
you think that an insert-or-update should be possible?

ISTM that you could try the UPDATE first and notice whether it updates
any rows or not.  I'm not sure what you're going to do if it doesn't,
though, since you still haven't got enough data to do an INSERT.

            regards, tom lane