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

Display Null Query Result With an Alternate Text

In vsql the /pset meta-command is used to set options that control how Vertica formats query result output. One of my favorites is the null option which allows you to specify an alternative value for null in the result set of a query.


dbadmin=> \pset NULL 'Unknown';
\pset: unknown option: NULL

That didn’t work! Make sure to use a lower case null keyword!

dbadmin=> \pset null 'Unknown'
Null display is "Unknown".

dbadmin=> SELECT null "Is there life on Europa?";
Is there life on Europa?

This setting works with any data type!

dbadmin=> CREATE TABLE null_test (some_int INT, some_varchar VARCHAR(10), some_date DATE);

dbadmin=> INSERT INTO null_test SELECT 1, 'TEST1', NULL;
(1 row)

dbadmin=> INSERT INTO null_test SELECT 2, NULL, NOW();
(1 row)

dbadmin=> INSERT INTO null_test SELECT NULL, 'TEST3', NOW()+1;
(1 row)

dbadmin=> SELECT * FROM null_test;
some_int | some_varchar | some_date
        1 | TEST1        | Unknown
        2 | Unknown      | 2018-10-16
  Unknown | TEST3        | 2018-10-17
(3 rows)

Helpful Link:

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.