Postgres Conditionals: How to Use Coalesce
How to use the "coalesce" conditional 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:
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.
```sql
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.
```sql
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.
```sql
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.
```sql
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!
Watch video Postgres Conditionals: How to Use Coalesce online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 03 October 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 1,764 once and liked it 13 people.