Vertica Quick Tip: Determining Table Columns that Contain NULL Values

Posted May 22, 2018 by Soniya Shah, Information Developer

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!