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?

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