Avoid a Division by Zero Error with the NULLIF and NULLIFZERO Functions

[Deleted User]

Jim Knicely authored this tip.

The NULLIF built-in Vertica function compares two expressions. If the expressions are not equal, the function returns the first expression. If the expressions are equal, the function returns NULL.

The NULLIFZERO built-in Vertica function evaluates to NULL if the value in the column is 0.

Both come in handy when you want to avoid a “Division by zero” error.

Example:

dbadmin=> SELECT 1 / 0 "This errors!";
ERROR 3117:  Division by zero

dbadmin=> SELECT 1 / NULLIFZERO(0) "This does not error!";
 This does not error!
----------------------

(1 row)

dbadmin=> SELECT 1 / NULLIF(0, 0) "This also does not error!";
 This does not error!
----------------------

(1 row)

IMHO, the NULLIFZERO function is a bit more “self-documenting” for this particular use case.

Have fun!