PostgreSQL Conditional Select With CASE Statements

By /
Conditional Select

If you have writing queries in PostgreSQL for longer, and you want to select data based on specific conditions in PostgreSQL, you can use the CASE statement within your SELECT query to perform conditional selection based on specific criteria. In this tutorial, you will learn about the CASE statement in PostgreSQL and explore how it enables users to selectively retrieve data based on specific conditions

Understanding Conditional Select

Assume a scenario where you are developing a website and for that, you want to retrieve data of users from a database table based on specific criteria. Conditional selection can turn this task handy as it allows you to filter and categorize data according to various factors like column value or logical expressions. You can optimize your query performance and fetch insights from datasets by selecting only the data that meets certain conditions.

Introduction to CASE Statement

Let’s say you are organizing a database and require a flexible way to do the task effectively, that’s where the CASE statement comes into the picture. It allows you to evaluate multiple conditions and return different results based on the outcome of these conditions. This enables you to customize query results based on specific criteria, which will enhance the effectiveness of your database operations.

Also Read: MySQL CASE Function and CASE Statement

Syntax

Below is the syntax for the CASE statement:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
ENDCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

  • Each WHEN clause evaluates a condition, and if the condition is true, it returns the corresponding result.
  • If none of the conditions match, the ELSE clause returns the default result.

Using CASE for Conditional Selection

One of the primary use cases of the CASE statement is conditional selection.

Example 1

Let’s consider the table given below to understand this better.

KIds Record
Kids Record

Let’s say we want to categorize kids into different categories based on their age. We can do that using the below query:

SELECT student_name, age, 
CASE WHEN age >= 18 THEN 'Adult' 
     WHEN age >= 13 THEN 'Teenager' 
     ELSE 'Child' 
END AS age_group 
FROM kids;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output

Categorized Data
Categorized Data

In the above example, we have used the CASE statement to categorize students into different groups.

  • If a student’s age is 19 or above, they are categorized as Adult.
  • If a student’s age is between 13 and 17, they are categorized into Teenager.
  • If a student’s age is below 13, they are categorized into Child.

The result of the CASE statement is aliased as “age_group”.

Example 2

Let’s take another scenario where we want to calculate the bonus marks which they get based on their day-to-day activity in school from the below table.

KIds Activity Record
Kids Activity Record

Now, to do that, we will use the below query:

SELECT student_name, age, 
CASE WHEN participates_in_extracurriculars THEN 5
     WHEN leadership THEN 3
 WHEN improvement THEN 2
     ELSE 0 
END AS bonus_marks 
FROM kids_activity;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output

KIds Bonus Record
Kids Bonus Record

In the above example, we have used the CASE statement to calculate the bonus marks for each student based on the below criteria.

  • If students participate in extracurricular activities, they get 5 bonus marks.
  • If a student shows leadership qualities, they get 3 bonus marks.
  • If the students show improvement in grades, they get 2 bonus marks.
  • And if none of the above conditions are met by students, they get 0 bonus marks.

Read More: CASE Statement with SUM() Function in SQL

Conclusion

Conditional selection in PostgreSQL, enabled by the CASE statement, allows users to retrieve and categorize data based on specific criteria. Users may adapt their data retrieval processes to satisfy a variety of requirements and derive useful insights from their datasets by utilizing conditional logic in SQL queries. Gaining proficiency with conditional select techniques gives PostgreSQL users the ability to get the most out of their database investments. In this tutorial, we have learned conditional selection in PostgreSQL with practical examples. We hope you enjoyed it.

Reference

https://stackoverflow.com/questions/76736686/postgres-case-expression-with-a-where-clause