The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Tip: How to Query for NaN Values

edited January 2018 in Tips from the Team

This tip was authored by Jim Knicely.

We’re introducing a new series: Vertica Quick Tips! These tips are intended to give you concise information to help you get the most out of using Vertica.

NaN (Not a Number) does not equal anything, not even another NaN. You can query for them using the predicate … WHERE column != column

For example:

dbadmin=> create table NaN_Test (c1 float);
CREATE TABLE
dbadmin=> insert into NaN_Test select 'NaN';
OUTPUT
--------
      1
(1 row)
dbadmin=> select * from NaN_Test;
c1
-----
NaN
(1 row)
dbadmin=> select * from NaN_Test where c1 = 'NaN';
c1
----
(0 rows)
dbadmin=> select * from NaN_Test where c1 != c1;
c1
-----
NaN
(1 row)
Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.