How to check if number is NaN

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

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

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;

Tags:

Postgresql

Nan