All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
This query only executes the
UPDATE part only if either
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));
COALESCE() never kicks in for this particular setup.
column1, being defined
NOT NULL, cannot be
NULL, neither in the table nor in the input
column2 has to be NULL or nothing happens. Hence we can never reset
This query only executes the
UPDATE part if either
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
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:
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂