Display Null Query Result With an Alternate Text: Quick Tip

Posted October 16, 2018 by Phil Molea, Sr. Information Developer, Vertica

Jim Knicely authored this post.

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:


Have fun!