Transcript

A Postgres case statement is a conditional statement similar to if/else statements in other programming languages. Case statements follow a when/then/else flow. Let's see how this works.

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

\d users

select * from users;

You'll notice that our users have a posts count. If we wanted to read from our table, and return something like a contribution rating for our users based on the number of posts they have made, we could use a case statement.

Let's try this out.

I'm going to use the "\e" command to use VIM as my query editor.

\e

We'll start by selecting name and posts count from our table, then we'll add in our case statement to determine contribution_rating.

As basic case statement can accept an expression, and evaluate that expression against conditionals in the when clauses. So if we pass posts count to our case statement, and specify a number in our when clause, we're checking for an exact match between our posts count and our value in the when clause.

select name,
posts_count,
case posts_count
  when 0 then 'Novice'
end as contribution_rating
from users;

As we can see in our output here, only one of our users met the when clause condition from our case statement. The rest of our users have a null value for their contribution rating.

To avoid null values in our case statement output, we can provide an else clause. The else clause specifies the returned value from a case statement if none of the when clause conditions were met.

Here, we're just going to return "Unknown".

select name,
posts_count,
case posts_count
  when 0 then 'Novice'
  else 'Unknown'
end as contribution_rating
from users;

Now, we can see that all of our users have a contribution rating, but all but one of them is "Unknown." Clearly we need a different approach, so we don't have to directly match every possible outcome from our posts count.

To do this, we're going to change things up a bit. Instead of supplying an expression to our case statement to use to evaluate against our when clauses, we can move all of our logic directly into the when clauses.

In our when clauses, we can include subexpressions. For us, this means we can use inequalities to get more meaningful output for our contribution rating.

So, if a user has more than 30 posts, their rating is "Gold". If they have more than 10 posts, their rating is "Silver."

You'll notice that for our second when clause, I'm not specifying that the posts count also has to be less than the value from our first when clause. This is because case statements in Postgres will return on the first matching when clause; all following when clauses will not be evaluated.

select name,
posts_count,
case
  when posts_count > 30 then 'Gold'
  when posts_count > 10 then 'Silver'
  when posts_count > 0 then 'Bronze'
  when posts_count = 0 then 'Novice'
  else 'Unknown'
end as contribution_rating
from users;

From the output now, we can see that all but one of our users has a known contribution rating. Our last user with "Unknown" has a null value for our posts count. So what happens if we want users with no posts count data to also be filtered into the "Novice" contribution rating?

We can do this by expanding on our last when clause's subexpression, adding an "or" and including a check for when posts count is null.

select name,
posts_count,
case
  when posts_count > 30 then 'Gold'
  when posts_count > 10 then 'Silver'
  when posts_count > 0 then 'Bronze'
  when posts_count = 0 or posts_count is null then 'Novice'
  else 'Unknown'
end as contribution_rating
from users;

We can now see that our user with the null value for posts count has been given the "Novice" contribution rating.

Thanks for watching!

Setup

create table users (
  id serial primary key,
  name varchar not null,
  posts_count integer
);

insert into users (name, posts_count)
values ('Abe', 0), ('Boris', 16), ('Cameron', 3), ('Dave', 7), ('Eve', 54), ('Frank', null);