Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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.


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!

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.