Vertica Quick Tip: Determining Table Columns that Contain NULL Values

This blog post was authored by Jim Knicely.

A client recently asked if we had a Vertica function that could return a list of columns from a table which contain NULL values. There aren’t any built-in functions for that, but there is always a fast solution available in Vertica!

Here’s one!

Example: dbadmin=> SELECT * FROM null_cols; c1 | c2 | c3 | c4 | c5 ----+------+-----+----+------- | TEST | 1.1 | 1 | STUFF | TEST | | 1 | STUFF | | | 1 | STUFF 1 | TEST | 1.1 | 1 | STUFF 1 | TEST | 1.1 | 1 | STUFF 1 | TEST | | 1 | STUFF 1 | | | 1 | STUFF (7 rows) dbadmin=> CREATE OR REPLACE VIEW null_cols_vw AS dbadmin-> SELECT COUNT(c1) < COUNT(1) c1_has_nulls, dbadmin-> COUNT(c2) < COUNT(1) c2_has_nulls, dbadmin-> COUNT(c3) < COUNT(1) c3_has_nulls, dbadmin-> COUNT(c4) < COUNT(1) c4_has_nulls, dbadmin-> COUNT(c5) < COUNT(1) c5_has_nulls dbadmin-> FROM null_cols; CREATE VIEW dbadmin=> SELECT * FROM null_cols_vw; c1_has_nulls | c2_has_nulls | c3_has_nulls | c4_has_nulls | c5_has_nulls --------------+--------------+--------------+--------------+-------------- t | t | t | f | f (1 row) dbadmin=> INSERT INTO null_cols SELECT 1, 'TEST', 1.1, 1, NULL; OUTPUT -------- 1 (1 row) dbadmin=> UPDATE null_cols SET c1 = 1 WHERE c1 IS NULL; OUTPUT -------- 3 (1 row) dbadmin=> SELECT * FROM null_cols_vw; c1_has_nulls | c2_has_nulls | c3_has_nulls | c4_has_nulls | c5_has_nulls --------------+--------------+--------------+--------------+-------------- f | t | t | f | t (1 row) Have Fun!