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

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!