Transcript

The coalesce function in Postgres accepts a list of arguments, and will return the first argument in the list that is not null. Let's try this out.

In this example, I have a test table called posts with a few entries.

\d posts

As you can see from inspecting the table, we have two columns relating to description. If we wanted to read from the table and return the short description if present, otherwise the full description, we would make use of the coalesce statement, passing as its arguments short_description first, followed by description.

select coalesce(short_description, description) as display_description from posts;

If all the values provided to coalesce are null, the statement will return null. We can see this when we just try to coalesce author.

select coalesce(author) from posts;

In this case, we can use coalesce to provide a default value for the author name, should the author not be present.

select coalesce(author, 'Unknown') from posts;

A small caveat to coalesce is that you cannot mix data types in the arguments for coalesce. We can see this if we use our release date field and try to give a default value of "Draft" if the date is null.

select coalesce(release_date, 'Draft') from posts;

As you can see, Postgres throws an error because our string, "Draft", is not a valid date.

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 Coalesce',
  'In this episode of PG Casts, we are going to explore the COALESCE conditional statement in Postgres',
  'In this episode we explore the COALESCE statement',
  null,
  'Mary Lee',
  '2019-01-31'
), (
  'This is a post',
  'This description is already short',
  null,
  'Content goes here',
  null,
  null
);