Transcript

In this episode I am going to show you how we can come up with a better display character for null values. But before we get in to that, let's look at an example to help us understand why we even need a display character for the null value.

Let's say we have a users table:

create table users (
  id serial primary key,
  email varchar not null unique,
  first varchar,
  last varchar
);

With a couple users:

insert into users (email, first, last)
  values ('lizlemon@nbc.com', 'Liz', 'Lemon');

insert into users (email) values ('jack.donaghy@nbc.com');

insert into users (email, first)
  values ('kenneth.parcell@nbc.com', 'Kenneth');

insert into users (email, first, last)
  values ('grizz@nbc.com', 'Grizz', '');

Now, let's take a look at all of the records in our users table:

table users;
id |          email          |  first  | last
----+-------------------------+---------+-------
1 | lizlemon@nbc.com        | Liz     | Lemon
2 | jack.donaghy@nbc.com    |         |
3 | kenneth.parcell@nbc.com | Kenneth |
4 | grizz@nbc.com           | Grizz   |

Some last name columns have been left null, but one has the value of an empty string. We cannot tell them apart though. The problem is that psql is displaying null values with a blank string which is indistinguishable from an actual blank string.

Browsing data like this in psql is going to get frustrating very quickly.

We need a better null display character, something that really stands out. My preference is the empty set symbol, Ø.

This can be set with the \pset meta-command.

\pset null 'Ø'

We can take another look at our user records to see the difference.

table users;
id |          email          |  first  | last
----+-------------------------+---------+-------
1 | lizlemon@nbc.com        | Liz     | Lemon
2 | jack.donaghy@nbc.com    | Ø       | Ø
3 | kenneth.parcell@nbc.com | Kenneth | Ø
4 | grizz@nbc.com           | Grizz   |

That's much better. We can easily distinguish between nulls and blank strings now.

I always want this option set when starting a psql session, so I go a step further by adding this to my .psqlrc file.

You may be wondering if there is any meaningful difference between a blank string and a null value or why we are allowing null values in the first place. These are both questions for another episode.

Until then, thanks for watching.