From: | Andreas Terrius <gotenwinz99(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Partial update on an postgres upsert violates constraint |
Date: | 2016-11-18 06:13:39 |
Message-ID: | CA+gNo8u8D4TsjumN-Em+9Cf=Ui4QysEfbJWKDnjxMUOZa-Jgww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Anns | 2016-11-18 06:29:45 | Re: How the Planner in PGStrom differs from PostgreSQL? |
Previous Message | Rakesh Kumar | 2016-11-18 01:10:27 | Re: pgbench and scaling |