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 NaN
s 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;