CODEWITHSUNDEEP
postgresql
Xyten
Xyten

Reputation: 83

Partial update on an postgres upsert violates constraint

I want to be able to upsert partially inside postgres (9.5), but it seems that a partial upsert fails when not all of the constraint is fulfilled (such as the not null constraint)

Here is an example of the scenario 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 go around approaching this ?

Upvotes: 5

Views: 786

Answers (1)

indiri
indiri

Reputation: 332

To think of it another way, what if the id didn't already exist? You can't insert just a phone number as it would have no name/address but that's exactly what you are telling it to do. So the constraint gets mad and it fails because an upsert tries to insert first and then updates if the insert fails. But your insert didn't get past the constraint check to see if it already existed.

What you can do instead if you want partials is tell it how to handle partials that would violate the constraints. Something like this (this is NOT complete and doesn't handle all partial data scenarios):

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;

Upvotes: 1

Related Questions