Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Determining Table Columns that Contain NULL Values

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.