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.