For floating point values which are represented as undefined or unrepresentable, Vertica can store NaN (not a number) values. In comparisons against NaN, results will return false whenever NaN is involved. This quick tip will show how to include or ignore NaN values.
First, a table with NaN is needed:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

dbadmin=>CREATETABLEtbl(afloat);

CREATETABLE

dbadmin=>INSERTINTOtbl(a)VALUES('NaN'::float);

OUTPUT

--------

1

(1row)

dbadmin=>INSERTINTOtbl(a)VALUES(1);

OUTPUT

--------

1

(1row)

dbadmin=>INSERTINTOtbl(a)VALUES(2);

OUTPUT

--------

1

(1row)

dbadmin=>COMMIT;

COMMIT

dbadmin=>SELECT*FROMtbl;

a

-----

NaN

1

2

(3rows)

Next, to find values with NaN, column <> column is used:

1

2

3

4

5

dbadmin=>SELECT*FROMtblWHEREa<>a;

a

-----

NaN

(1row)

Lastly, to ignore values with NaN, column = column is used:

1

2

3

4

5

6

dbadmin=>SELECT*FROMtblWHEREa=a;

a

---

1

2

(2rows)

On a side note, this would probably make for a fun interview question.

About the author /

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights.
The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.