PostgreSQL CASE Statement

Introduction to Case Statement in PostgreSQL

The case statement in PostgreSQL is used to apply conditional logic within SQL queries. It allows you to perform different actions based on various conditions, similar to the "if-else" construct in traditional programming languages. The case statement can be particularly useful when you want to derive new information or transform data based on specific conditions present in your database.

There are two main types of case statements in PostgreSQL: the simple case statement and the searched case statement. Let's dive into each of these types and explore their syntax and examples.

Simple CASE Statement

The simple case statement is used when you have a single expression to compare with multiple possible values. It follows a straightforward syntax:

Syntax

The case expression is evaluated once, and each WHEN clause compares the expression with a specific value. If a match is found, the corresponding result is returned. If no match is found, and an ELSE clause is provided, the else_result will be returned. If there is no ELSE clause, the case statement will return NULL.

Simple PostgreSQL CASE Expression Example

Let's say we have a table called "students" with columns "name" and "score". The following is the SQL query to create this table:

The next step is to insert some sample data into this table. Use the following SQL query to insert some sample data into this table:

To view the contents of this students table, use the following SQL query:

Output:

Example:

We want to categorize the students based on their scores into three groups: "Excellent" for scores above 90, "Good" for scores between 80 and 89, and "Average" for scores below 80. We can use the following SQL query to achieve this task:

Output:

In this example, the case statement evaluates the "score" column and assigns the corresponding grade based on the conditions defined in the WHEN clauses.

Using Simple PostgreSQL CASE Expression with Aggregate Function Example

You can also use the simple case statement with aggregate functions. Let's say we want to calculate the average grade for each group of students based on their grades.

Output:

Here, the case statement categorizes the students, and the AVG function calculates the average score for each group.

Searched CASE Statement

The searched case statement is used when you have multiple expressions or conditions to evaluate. It allows for more complex comparisons and provides greater flexibility compared to the simple case statement. The syntax for the searched case statement is as follows:

Syntax

Each WHEN clause in the searched case statement has its boolean condition to check. In the first matching boolean condition, the corresponding result is returned. If no match is found and an ELSE clause is provided, the else_result will be returned. If there is no ELSE clause, the case statement will return NULL.

Searched CASE Statement Example

Let's consider a scenario where we have a table called "employees" with columns "name," "age", and "salary". We can use the following SQL query to create this table:

The next step is to insert some sample data into the employee's table that we have created in Step 1. We can use the following SQL query to insert data into the table:

To view the contents of this table, we can use the following SQL query:

We want to categorize the employees based on their age and salary into different job levels: "Junior", "Intermediate", and "Senior".

Output:

In this example, the searched case statement evaluates multiple conditions to determine the job level of each employee based on their age and salary.

Using CASE with an Aggregate Function Example

Similar to the simple case statement, the searched case statement can also be used with aggregate functions. Let's say we want to find the average salary for each job level in the "employees" table.

Output:

In this query, the searched case statement categorizes the employees, and the AVG function calculates the average salary for each job level.