Can't set value to column conditionally

Question

The following code throws a can't calculate log of zero error even though I check for that case in x != N

DO $$
DECLARE N integer;
BEGIN
    select count(*) from mytable INTO N;
    UPDATE
        words
    SET
    measure = 
         CASE 
            WHEN x != N THEN 1 + log((N - x) / x)
            ELSE 0
         END;   
END $$;

also tried this, same error:

DO $$
DECLARE N integer;
BEGIN
    select count(*) from mytable INTO N;
    UPDATE words
    SET measure = 1 + log((N-x)/x)
    WHERE x <> N;   
END $$;

What's the datatype for x ? If that's an integer, (N-x)/x might become 0. — Frank Heikens, Nov 29 '21 at 16:37

score 1 · Accepted Answer · answered Nov 29 '21 at 16:39

I think the cause is in the "(N-x)/x" part: Since both 'N' and 'x' are integers, the result is rounded down to the nearest integer (which is always 0 for positive 'N-x' and 'x').

BTW, this is a duplicate question:

Division ( / ) not giving my answer in postgresql

Can't set value to column conditionally

1 Answers1