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!
Смотрите видео Postgres Conditionals: How to Use Coalesce онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь PG Casts by Hashrocket 03 Октябрь 2019, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 1,764 раз и оно понравилось 13 людям.