Partial update on an postgres upsert violates constraint
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
"
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general