Postgres Conditionals: How to Use Nullif
How to use the "nullif" statement in PostgreSQL. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
Hey everyone, today we're going to explore the "nullif" statement in Postgres.
The nullif statement in Postgres accepts two arguments, and returns null if the two arguments are equal, otherwise returns the first argument.
Let's take a look at how this works.
For this example, I have a table called posts with a few entries.
```sql
select * from posts;
```
If we want to remove blank values from our short_description column when reading from the table, we can use the nullif statement, passing as our first argument short_description, followed by an empty string as our second argument.
```sql
select nullif(short_description, '') as short_description from posts;
```
As you can see from the output, our row where short_description wasn't blank returned the short_description. However, our row with the blank short_description now returns null instead of the empty string.
The nullif statement can be very helpful when used in conjunction with coalesce statements. For example, if we wanted to read from our table and return short_description if present, otherwise description:
```sql
select coalesce(short_description, description) as display_description from posts;
```
We can see that the empty string short_description does not evaluate to null in the coalesce statement, and so we have blank values in our output.
To resolve this, we can use the nullif statement in conjunction with the coalesce, using it as the first argument to the coalesce, and passing it our short description, followed by an empty string.
```sql
select coalesce(nullif(short_description, ''), description) as display_description from posts;
```
From the output, we can see now that both of our rows have a display description.
Thanks for watching!
Watch video Postgres Conditionals: How to Use Nullif online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 17 October 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 742 once and liked it 13 people.