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.

select * from posts;

If we want to remove blank values from our shortdescription column when reading from the table, we can use the nullif statement, passing as our first argument shortdescription, followed by an empty string as our second argument.

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: 

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.

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!

Setup

create table posts (
  id serial primary key,
  title varchar not null,
  description text,
  short_description varchar,
  content text,
  author varchar,
  release_date date
);

insert into posts (title, description, short_description, content, author, release_date)
values (
  'How to Use Nullif',
  'In this episode of PG Casts, we are going to explore the NULLIF conditional statement in Postgres',
  'In this episode we explore the NULLIF statement',
  null,
  'Mary Lee',
  '2019-01-31'
), (
  'This is a post',
  'This description is already short',
  '',
  'Content goes here',
  null,
  null
);