Re: [GENERAL] Partial update on an postgres upsert violates constraint

[Tom Lane]

Andreas Terrius  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partial update on an postgres upsert violates constraint

[John R Pierce]

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partial update on an postgres upsert violates constraint

[Andreas Terrius]

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



On Sun, Nov 20, 2016 at 3:57 AM, Tom Lane  wrote:

> Adrian Klaver  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
>

Re: [GENERAL] Partial update on an postgres upsert violates constraint

[Adrian Klaver]

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

Adrian Klaver > 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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partial update on an postgres upsert violates constraint

[Tom Lane]

Adrian Klaver  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partial update on an postgres upsert violates constraint

[Adrian Klaver]

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.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partial update on an postgres upsert violates constraint

[Kim Rose Carlsen]

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

Re: [GENERAL] Partial update on an postgres upsert violates constraint

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Partial update on an postgres upsert violates constraint

[Andreas Terrius]

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,

CREATE TABLE jobs (
id integer PRIMARY KEY,
employee_name TEXT NOT NULL,
address TEXT NOT NULL,
phone_number TEXT);
CREATE OR REPLACE FUNCTION upsert_job(job JSONB)
RETURNS VOID AS $$BEGININSERT INTO jobs AS origin VALUES(
(job->>'id')::INTEGER,
job->>'employee_name'::TEXT,
job->>'address'::TEXT,
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;$$ LANGUAGE PLPGSQL SECURITY DEFINER;

--Full insert (OK)SELECT upsert_job('{"id" : 1, "employee_name" :
"AAA", "address" : "City, x street no.y", "phone_number" :
"123456789"}'::jsonb);
--Partial update that fulfills constraint (Ok)SELECT upsert_job('{"id"
: 1,  "employee_name" : "BBB", "address" : "City, x street
no.y"}'::jsonb);
--Partial update that doesn't fulfill constraint (FAILS)SELECT
upsert_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 ?

Thank you