CODEWITHSUNDEEP
postgresqlnullsumaggregate-functionsnan
Ludo
Ludo

Reputation: 2527

Postgresql - how to treat NaN as 0 when summing?

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?

Upvotes: 4

Views: 6380

Answers (1)

klin
klin

Reputation: 121764

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)

Upvotes: 11

Related Questions