Determining Table Columns that Contain NULL Values

[Deleted User]

Jim Knicely authored this tip.

A client recently asked if we had a Vertica function that could return a list of columns from a table that contains 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!