# PostgreSQL CASE Statement - Explained With Examples

> 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 Searc…

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-case-statement-explained-with-examples/)