Vertica Quick Tip: How to Query for NaN Values

Posted January 10, 2018 by Soniya Shah, Information Developer

This blog post 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)