Display Null Query Result With an Alternate Text

Jim_Knicely · October 2018

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.

Example:

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?
--------------------------
Unknown

This setting works with any data type!

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

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

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

dbadmin=> INSERT INTO null_test SELECT NULL, 'TEST3', NOW()+1;
OUTPUT
--------
      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:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Meta-Commands/psetNAMEVALUE.htm

Have fun!