Postgresql - how to treat NaN as 0 when summing?

Question

I have a table that contains numerical values and NaNs. When summing, if the selected values contain a NaN, the result will be NaN. Is there a way to make postgresql treat them as 0s when summing rather than NaN? Or do I just have to convert all NaNs in table to 0 or Null?

Answer 1

Use NULLIF, example:

with my_table(col) as (
values
    (1::numeric),(2),('NaN')
)

select sum(col) as original, sum(nullif(col, 'NaN')) as corrected
from my_table;

 original | corrected 
----------+-----------
      NaN |         3
(1 row)