By default, when a query returns a null value in the DuckDB command line interface (CLI), an empty string is displayed. This may or may not be what you want. But if you’re like me, you probably want DuckDB to explicitly tell you that it’s a null value. After all, if an empty string is returned, perhaps the data contained an empty string?

Fortunately, DuckDB provides us with the .nullvalue dot command so that we can change the output of null values.

The Default Output

To demonstrate the default behavior of DuckDB returning an empty string for null values, we can open a new connection to DuckDB and run the following query:

SELECT null;

Result:

+------+
| NULL |
+------+
|      |
+------+

We can see that an empty string is returned. The column header contains NULL but that’s not the output.

Changing NULL Output

We can use .nullvalue to set a string to be output whenever a null value is encountered:

.nullvalue 'null'

Now let’s run the query again:

SELECT null;

Result:

+------+
| NULL |
+------+
| null |
+------+

This time the query returned the string null.

It doesn’t have to be null, it could be any string. For example, it could be n/a, unknown, etc:

.nullvalue 'unknown'
SELECT null;

Result:

+---------+
|  NULL   |
+---------+
| unknown |
+---------+

And of course, we can simply SELECT null any time we want to find out what the current value of the .nullvalue dot command is.