A Better Null Display Character
This PG Casts episode is brought to you by Hashrocket, a consultancy specializing in PostgreSQL - learn more at https://hashrocket.com. To see more PG Casts videos, visit our YouTube channel or https://www.pgcasts.com
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:
```sql
create table users (
id serial primary key,
email varchar not null unique,
first varchar,
last varchar
);
```
With a couple users:
```sql
insert into users (email, first, last)
values ('[email protected]', 'Liz', 'Lemon');
insert into users (email) values ('[email protected]');
insert into users (email, first)
values ('[email protected]', 'Kenneth');
insert into users (email, first, last)
values ('[email protected]', 'Grizz', '');
```
Now, let's take a look at all of the records in our `users` table:
```sql
table users;
id | email | first | last
---+-------------------------+---------+------
1 | [email protected] | Liz | Lemon
2 | [email protected] | |
3 | [email protected] | Kenneth |
4 | [email protected] | 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.
```sql
\pset null 'Ø'
```
We can take another look at our user records to see the difference.
```sql
table users;
id | email | first | last
---+-------------------------+---------+------
1 | [email protected] | Liz | Lemon
2 | [email protected] | Ø | Ø
3 | [email protected] | Kenneth | Ø
4 | [email protected] | 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.
Смотрите видео A Better Null Display Character онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь PG Casts by Hashrocket 15 Июнь 2016, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 1,346 раз и оно понравилось 16 людям.