Can't set value to column conditionally

November 29, 2021

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 $$;

>Solution :

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