Null value in column "logit" violates not-null constraint

Question

I am using Greenplum (based on PostgreSQL 8.2.15) and my update command:

update table01
set logit=
case
when sex = '03' then
logit+0.5*0.8
when sex = '@0' then
logit+0.5*0.2
when sex = '02' then
logit+0.5*0.4
when sex = 'N' then
logit+0.5*(-1.6)
when sex = '01' then
logit+0.5*(-0.4)
end;

It offered me an error:

null value in column "logit" violates not-null constraint

The "logit" column type is double precision and except when sex = 'N' returns no rows, other conditions have ones.

I have checked other answers concerned about this error but did not get help yet. Why does this occur and how to fix it?

Answer 1

You want to set the update to match the case statements that have values:

update table01
    set logit = (case when sex = '03' then logit+0.5*0.8
                      when sex = '@0' then logit+0.5*0.2
                      when sex = '02' then logit+0.5*0.4
                      when sex = 'N' then logit+0.5*(-1.6)
                      when sex = '01' then logit+0.5*(-0.4)
                 end)
    where sex in ('03', '@0', '02', 'N', '1');

Alternatively, you can keep the value the same explicitly in the set:

update table01
    set logit = (case when sex = '03' then logit+0.5*0.8
                      when sex = '@0' then logit+0.5*0.2
                      when sex = '02' then logit+0.5*0.4
                      when sex = 'N' then logit+0.5*(-1.6)
                      when sex = '01' then logit+0.5*(-0.4)
                      else logic
                 end);

The first version is more efficient. The second is easier to maintain.