Partial update on postgres upsert violates restriction

Question

I want to partially restore part of postgres (9.5), but it seems that partial upsert fails when not all constraints are fulfilled (for example, not a null constraint)

Here is an example script and error

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

How do I get closer to this?

Answer 1

-, , ? , /, , . , , , upsert , , . , , .

, , , , . - ( ):

CREATE OR REPLACE FUNCTION upsert_job(job JSONB)
RETURNS VOID AS $$
BEGIN
IF (job->>'phone_number' IS NOT NULL 
    AND job->>'employee_name' IS NOT NULL 
    AND job->>'address' IS NOT NULL) THEN
    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);
ELSIF (job->>'phone_number' IS NOT NULL AND (job->>'employee_name' IS NULL AND job->>'address' IS NULL)) THEN
    UPDATE jobs SET phone_number=job->>'phone_number'::TEXT
    WHERE id=(job->>'id')::INTEGER;
END IF;

END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER;