Conditional statements are the core concepts in any programming paradigm. These statements include if, if-else, case, etc. The CASE statement is one of the conditional expressions that is used to create conditional queries. PostgreSQL allows us to use the WHEN-THEN case, if-else statements, etc. with the CASE statement to create or formulate a query/expression.

This post illustrates several use cases of the CASE statement in PostgreSQL via practical examples.

What is CASE Statement and How to Write it in Postgres?

As stated earlier, the CASE statement is a conditional expression, so it can be used with any statement or clause where an expression can be used, such as a WHERE clause, SELECT statement, etc. Postgres supports two forms of the CASE statement: A Searched CASE and a Simple CASE.

Basic Syntax: Searched CASE Statement

The below snippet depicts the syntax of a searched CASE statement:

CASE 
    WHEN cond_1  THEN res_1
    WHEN cond_2  THEN res_2
    …
    WHEN cond_n THEN res_n
    ELSE else_result
END

Let’s comprehend the above syntax line-by-line:

- The conditions, such as codition_1, condition_2, …, condition_n, are the boolean conditions that will retrieve either true or false.
- The CASE statement will execute each condition specified within the CASE statement from top-to-bottom.
- When a condition retrieves a “TRUE” value then the result associated with that condition will be retrieved. For instance, when “condition_1” becomes true, then “result_1” will be retrieved.
- Once the CASE statement encounters that an expression retrieves a TRUE value, then immediately, it will stop evaluating the remaining expressions.
- However, if a condition retrieves a “FALSE” value, then the CASE expression will evaluate the next condition. This process will continue until an expression retrieves true.
- If all the conditions/expressions retrieve “FALSE” then the result associated with the “ELSE” block will be executed.

Basic Syntax: Simple CASE Statement

The below snippet illustrates the syntax of a simple CASE statement:

CASE search_expression
    WHEN cond_1  THEN res_1
    WHEN cond_2  THEN res_2
    …
    WHEN cond_n THEN res_n
    ELSE else_result
END

In the above syntax:

- The “Search_expression” represents an expression to be evaluated against the expression specified in each “WHEN” case.
- When a condition retrieves a “TRUE” value then the result associated with that condition will be retrieved. For instance, if “condition_1” satisfies the “search_expression” then “result_1” will be retrieved.

Example 1: How Does the Searched CASE Statement Work in Postgres?

In this example, we will apply the CASE statement on the “author_info” table, whose data is shown in the following snippet:

SELECT * 
FROM author_info
ORDER BY author_id ASC;

In the following statement, we will use the CASE expression to check if the author is a “freshman” “junior”, “senior”, or “expert”:

SELECT author_name, author_exp,
CASE 
WHEN author_exp >= 0 AND author_exp <= 1 
THEN 'Freshman'
WHEN author_exp > 1 AND author_exp <= 3 
THEN 'Junior Author'
WHEN author_exp > 3 AND author_exp <= 5 
THEN 'Senior Author'
WHEN author_exp > 5 THEN 'Expert'
END CASE
FROM author_info
ORDER BY author_id ASC;

The output shows the usage of the CASE statement in PostgreSQL.

Example 2: How Does the Simple CASE Statement Work in Postgres?

The following snippet demonstrates the working of the simple CASE statement:

SELECT author_name, author_exp,
CASE gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END CASE
FROM author_info
ORDER BY author_id ASC;

In the above snippet, the CASE statement is used to add the gender description to the output:

This is how the Simple CASE Statement works in PostgreSQL.

Example 3: How to Use the CASE Statement With Aggregate Functions in Postgres?

Postgres allows us to use the simple and searched CASE statements with the aggregate functions. In the below-provided query, we will use the COUNT() function to count the number of “junior”, and “senior” authors:

SELECT
COUNT(CASE 
WHEN author_exp >= 0 AND author_exp <= 4 
THEN 'Junior Author'
END) As Junior_authors,
COUNT(CASE 
WHEN author_exp >= 5 
THEN 'Senior Author'
END) As Senior_authors
FROM author_info;

This way, you can use any aggregate function with the CASE statement to achieve different functionalities.

Conclusion

The CASE statement is one of the conditional expressions that is used to create conditional queries. Postgres supports two forms of the CASE statement: A Searched CASE and a simple CASE. We can use the WHEN-THEN case with the CASE statement to create or formulate a query/expression. It can be used with any statement or clause where an expression can be used, such as with a WHERE clause, with the SELECT statement, etc. This post has presented various examples to explain the usage of the case statement in PostgreSQL.