Tip: How to Query for NaN Values
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
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)