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 $$
BEGIN
INSERT 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