PostgreSQL UPSERT – Issue with NOT NULL Columns

postgresqlupsert

I want o know if it's possible to do an upsert when one of the columns (column1 or column2) is null, I just need a partial update, and of course, I'm trying to avoid a SELECT to verify if the record already exists.

This is an example of what I'm trying to achieve.

CREATE TABLE test(
    column1 timestamp NOT NULL,
    column2 uuid,
    status integer NOT NULL,
    id uuid NOT NULL,
    CONSTRAINT upsert_conflict UNIQUE (id, status)
);

And I have something like this:

INSERT INTO test(column1, status, id) 
VALUES(now(), 0, <any uuid>)
ON CONFLICT ON CONSTRAINT upsert_conflict 
DO UPDATE SET column1= now()

This one works as expected, but I also have something like this:

INSERT INTO test(column2, status, id) 
VALUES(gen_random_uuid (), 0, <existing uuid>)
ON CONFLICT ON CONSTRAINT upsert_conflict 
DO UPDATE SET column2= gen_random_uuid ()

I know that the INSERT is wrong because we cannot set a NULL value to column1, but assuming that I already have a record with that id and status, I would like to do the UPDATE, instead of that, I'm getting an error: ...violates not-null constraint. because of the missing column.

I'm creating these queries dynamically according to what users pass me. Sometimes I will have something like the first example, and sometimes, something like the second example, and also I don't know which column must not be null.

I hope you can help me.

Best Answer

Your second example never makes sense, while column1 is defined NOT NULL. You must provide a value for the required column, or the command will fail before it can even check for unique violations.

Query 1

This query only executes the UPDATE part only if either column1 or column2 are NULL and at least one actually changes (anything actually changes), but it does not allow to revert either back to NULL:

INSERT INTO test AS t
       (column1, column2          , status, id) 
VALUES (now()  , gen_random_uuid(), 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = COALESCE(t.column1, EXCLUDED.column1)
     , column2 = COALESCE(t.column2, EXCLUDED.column2)
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM COALESCE(t.column1, EXCLUDED.column1)
    OR t.column2 IS DISTINCT FROM COALESCE(t.column2, EXCLUDED.column2));

But COALESCE() never kicks in for this particular setup. column1, being defined NOT NULL, cannot be NULL, neither in the table nor in the input VALUES. So column2 has to be NULL or nothing happens. Hence we can never reset column2 to NULL anyway.

Query 2

This query only executes the UPDATE part if either column1 or column2 are NULL and at least one is actually being changed -even if back to NULL:

INSERT INTO test AS t
       (column1, column2, status, id) 
VALUES (now()  , NULL   , 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = EXCLUDED.column1
     , column2 = EXCLUDED.column2
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM EXCLUDED.column1
    OR t.column2 IS DISTINCT FROM EXCLUDED.column2);

This time, actual input values are used, even if NULL. No COALESCE() that defaults to the existing value if the new one is NULL

db<>fiddle here

Either way, you can be sure that a row with (status, id) = (0, '5beb004a-63ec-4f01-8604-37296a208e5f') exists after executing the command successfully - even if that row might be missing from the set returned by a RETURNING clause. See:

Related:

Related Question