Posted on :: Min Read

The PostgreSQL interactive CLI has the wonderfully convenient \pset command, which can be invoked to modify the display output of query results.

One of the available \pset options allows you to set a fixed string as a placeholder for NULL values. This ends up being quite useful, since it's effectively impossible to visually differentiate a NULL column value and the empty string/a collection of space characters:

postgres=> SELECT null AS "Null Representation", '' AS "Empty String Representation";

 Null Representation | Empty String Representation
---------------------+-----------------------------
                     |
(1 row)

The same output, but after we invoke \pset to better distinguish NULL values, using our very creative <NULL> placeholder:

postgres=> \pset null '<NULL>'
Null display is "<NULL>".

postgres=> SELECT null AS "Null Representation", '' AS "Empty String Representation";

 Null Representation | Empty String Representation
---------------------+-----------------------------
 <NULL>              |

(1 row)

Much better!

There's always the chance that the text in the column(s) you're querying have <NULL> as an actual value, so keep that in mind.

Per Connection, User, and/or System

If this is something you want in all of your interactive sessions, you can set the option explicitly on startup:

$ psql -P 'null=<NULL>'  # additional args as necessary, e.g. -p <PORT> -h <host> -d <database>

If you absolutely, unequivocally, always want your custom display option set for every connection, you can create a user-specific ~/.psqlrc file to declare the options that will be read every time psql is invoked:

# source of ~/.psqlrc
\pset null <NULL>

There's also the system-wide psqlrc file, which is located in the directory returned by pg_config --sysconfdir (or can be created there if it does not already exist). Any options there will be set for all users, but a multi-user system that has a common install of psql probably isn't the norm anymore.