update of the null field

August 25, 2022

i have a table

create table control_answers
(
    first_id            uuid not null,
    second_id           uuid not null,
    control             integer,
    correct_answers     integer,
    constraint marker_skills_pk
        primary key (first_id, second_id)
);

control and correct_answers can be nulls.
I need to write two queries. The first increments two fields(control, correct_answers), the second only control.

control and correct_answers can be nulls. Because of this my queries doesn’t work

UPDATE control_answers
SET (control, correct_answers) = (control + 1, correct_answers + 1)
WHERE first_id = '00000000-0000-0000-0000-000000000011' AND
    second_id = '00000000-0000-0000-0000-000000000011';

>Solution :

This is expected in all SQL databases. Any operation on a NULL produces a NULL. That’s the SQL standard. NULL means UNKNOWN. It’s not 0, an empty string or missing value. It means we have no idea what the value is. What result do you get when you add 1 to an unknown number? You can’t know, ie NULL.

What if the field was named temperatureInC ? Not knowing doesn’t mean the temperature is 0. It would make no sense to get 1 C by adding 1 to an unknown temperature

Use COALESCE to replace NULL with an actual value, eg:

UPDATE control_answers
SET 
    control= COALESCE(control,0) + 1,
    correct_answers= COALESCE(correct_answers,0) + 1
WHERE first_id = '00000000-0000-0000-0000-000000000011' AND
    second_id = '00000000-0000-0000-0000-000000000011';