How to check if number is NaN

Question

I need to test if a numeric/float value in PostgreSQL is not a number (NaN). Note that "PostgreSQL treats NaN values as equal". As I'm not seeing any isnan function in PostgreSQL 9.3, here is my best attempt to make one:

create or replace function isnan(double precision) returns boolean as
  $$select $1::text = 'NaN'::text$$ language sql;

Is there any better way to test for NaNs?

Bonaparte

Answer 1

Is there any better way to test for NaNs?

Simply compare for equality:

SELECT double precision 'NaN' = double precision 'NaN';

as, per the docs you linked to, Pg treats NaNs as equal. I'm surprised by this, given that it correctly treats NULL as not equal to any other NULL, and would've expected NaN = NaN to return NULL, but ... well, it works.

Or if you want a function:

create or replace function isnan(double precision) 
language sql
immutable
returns boolean as $$
select $1 = double precision 'NaN'
$$;

Pudens

Being relatively new to plpgsql, it seems to me that since float == float8 == double precision (at least at the current time), you probably would want to pick one synonym and use it consistently. – Jockey

Answer 2

You can use nullif

select * from my_table where id = 209 and nullif(price, 'NaN') is not null;

Found this idea from this reply

https://mcmap.net/q/534770/-postgresql-how-to-treat-nan-as-0-when-summing

Instructions

Answer 3

Any NaN value taken with a minus sign still is a NaN value (just like a zero), so you can try this:

create or replace function isnan(double precision) returns boolean as $$
select $1 = -$1 and $1 != 0.0 $$ language sql;

or:

create or replace function isnan(double precision) returns boolean as $$
select $1 = -$1 and $1 = $1 + 1.0 $$ language sql;

As far as PostgreSQL treats NaN values greater than all non-NaN values, the following trick is possible:

create or replace function isnan(double precision) returns boolean as $$
select $1 > 0 and -$1 > 0 $$ language sql;

Marchand

Answer 4

You can also use typecasts to create an actual NaN value to compare against:

SELECT *
FROM (VALUES
    (1, 100),
    (2, 'NaN'::NUMERIC),
    (3, 300)
) A
WHERE column2 = 'NaN'::NUMERIC;

Which matches only the NaN values:

column1 | column2
--------+--------
      2 |    NaN

Liquate

Why doesn't this work when one substitutes INT for NUMERIC? – Capping

@Vérace: I would guess because NUMERIC is an IEEE float which has a specific binary value allocated for use as NaN, whereas INT is a normal integer where all possible binary values are usable numbers - there is no value set aside for use only as NaN. You would have to pick an INT value and decide for yourself to treat it as NaN, such as 0, -1, 65536 and so on. But it means you can't then use that number normally because you've now decided it means NaN. The NUMERIC type handles all that logic for you. – Liquate

Thanks for that - makes sense. I suppose one would have to delve into the source code to know for sure, but nice one! Makes sense! +1 – Capping

Answer 5

For Google BigQuery (and maybe other sql servers out there), use the following

SELECT * FROM [project:dataset.table]
WHERE IS_NAN(field) = true

Decoy

The question is about postgresql, where IS_NAN is not defined – Progressionist