Return Zero Instead of NULL: Quick Tip

Jim Knicely authored this post.

The built-in Vertica function ZEROIFNULL evaluates to 0 for NULL values in a table’s column.

Example: dbadmin=> CREATE TABLE zin (C INT); CREATE TABLE dbadmin=> INSERT INTO zin SELECT 1; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO zin SELECT NULL; OUTPUT -------- 1 (1 row) dbadmin=> SELECT c, zeroifnull(c) zin FROM zin; c | zin ---+----- 1 | 1 | 0 (2 rows) dbadmin=> SELECT zeroifnull(NULL); ERROR 3459: Function zeroifnull(unknown) is not unique HINT: Could not choose a best candidate function. You may need to add explicit type casts Whoa! What happened in that last statement? Since NULL by itself does not have a data type, you’ll need to cast it to a valid data type for the function! dbadmin=> SELECT zeroifnull(NULL::INT); zeroifnull ------------ 0 (1 row) Helpful link:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Null/ZEROIFNULL.htm

Have fun!