By default, null values are returned as an empty string in psql. But this can easily be changed.

One reason you might want to change this is to avoid null values being confused with actual empty strings.

You can change this with the \pset null 'value' command.

Example

Here’s an example to demonstrate:

\pset null '<null>'

Result:

Null display is "<null>".

It has now been changed so that null values will return <null> instead of the empty string.

Here’s an example of running a query that returns a null value:

SELECT null AS Result;

Result:

 result 
--------
 <null>
(1 row)

It returns the value that we set with the \pset null command.

Here it is compared to an empty string:

SELECT 
    null AS "Null Value",
    '' AS "Empty String";

Result:

 Null Value | Empty String 
------------+--------------
 <null>     | 
(1 row)

There’s no mistaking which one is the null value and which one is the empty string.

Let’s set \pset null back to the default value to see how confusing the empty string can be:

\pset null ''
SELECT 
    null AS "Null Value",
    '' AS "Empty String";

Here’s what it looks like when \pset null set to the default value:

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