CASE in PostgreSQL explained with examples

Databases are at the heart of most modern web applications, and PostgreSQL is one of the most popular relational database systems out there. One powerful feature of PostgreSQL—and SQL in general—is the CASE expression. It brings an element of conditional logic to the world of querying, allowing developers to define multiple outcomes based on specific conditions. This dynamic flexibility makes it an essential tool in the SQL toolkit.

Introduction

The CASE expression in SQL provides a mechanism for introducing conditional logic directly within queries. By using CASE, one can craft queries that return different results based on specific conditions, making it invaluable for dynamic data retrieval and manipulation. In a world where data is king, being able to adapt and conditionally interact with that data is crucial.

Basic Structure of CASE

The CASE expression comes in two main flavors: Simple and Searched.

Simple CASE

The Simple CASE form is reminiscent of a switch-case construct in traditional programming languages. It evaluates a single expression and returns a value based on a specified condition. Its structure is generally as follows:

CASE expression

    WHEN value1 THEN result1

    WHEN value2 THEN result2

    ...

    ELSE default_result

END;

Searched CASE

Unlike the Simple CASE that evaluates a single expression against multiple values, the Searched CASE evaluates multiple conditions. Its structure is:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE default_result

END;

Simple CASE Example

Suppose you have a table students with a column grades. If you want to classify students based on their grades, a Simple CASE can be handy.

Query Demonstration

SELECT name, grade,

CASE grade

    WHEN 'A' THEN 'Excellent'

    WHEN 'B' THEN 'Good'

    WHEN 'C' THEN 'Average'

    ELSE 'Needs Improvement'

END as performance

FROM students;

The output might look like:

 name  | grade |     performance     
-------+-------+---------------------
 Alice | A     | Excellent
 Bob   | C     | Average
 Eve   | B     | Good

Searched CASE Example

Using the same students table, let’s categorize students based on a range of grades.

Query Demonstration

SELECT name, score,

CASE

    WHEN score >= 90 THEN 'Excellent'

    WHEN score >= 70 AND score < 90 THEN 'Good'

    WHEN score >= 50 AND score < 70 THEN 'Average'

    ELSE 'Needs Improvement'

END as performance

FROM students;

Using CASE in Different Parts of a Query

The versatility of the CASE expression shines when it’s used in various segments of an SQL query.

In the SELECT Clause

You’ve already seen this in action in the previous examples, where we classified student performance based on grades or scores.

In the WHERE Clause

You can use CASE to create dynamic filters.

SELECT name, score

FROM students

WHERE

    CASE

        WHEN score >= 90 THEN true

        ELSE false

    END;

In the ORDER BY Clause

To conditionally order data:

SELECT name, score

FROM students

ORDER BY

CASE

    WHEN score >= 90 THEN 1

    ELSE 2

END;

In the GROUP BY Clause

Group data conditionally:

SELECT

CASE

    WHEN score >= 90 THEN 'High Scorers'

    ELSE 'Others'

END as category, COUNT(*)

FROM students

GROUP BY category;

With Aggregate Functions

Incorporate CASE with aggregate functions to gain more insights:

SELECT

CASE

    WHEN score >= 90 THEN 'High Scorers'

    ELSE 'Others'

END as category, COUNT(*), SUM(score)

FROM students

GROUP BY category;

Combining CASE with Other SQL Functions

One of the intriguing aspects of SQL is its ability to combine various functions for more intricate operations. In PostgreSQL, the CASE expression isn’t an exception. Let’s see how we can enhance its capability by coupling it with other functions.

String Functions

Imagine you want to classify user names based on their initials. You might use CASE along with the SUBSTRING function:

SELECT username,

       CASE

           WHEN SUBSTRING(username, 1, 1) BETWEEN 'A' AND 'M' THEN 'Group A-M'

           ELSE 'Group N-Z'

       END AS user_group

FROM users;

Furthermore, you can even combine results using the CONCAT function:

SELECT username,

       CONCAT('Belongs to: ',

           CASE

               WHEN SUBSTRING(username, 1, 1) BETWEEN 'A' AND 'M' THEN 'Group A-M'

               ELSE 'Group N-Z'

           END) AS user_classification

FROM users;

Date Functions

You can also harness the power of CASE with date functions. For instance, to categorize users by their age:

SELECT username, birthdate,

       CASE

           WHEN AGE(birthdate) < interval '20 years' THEN 'Teen'

           WHEN AGE(birthdate) BETWEEN interval '20 years' AND interval '40 years' THEN 'Adult'

           ELSE 'Senior'

       END AS age_group

FROM users;

Or to break down sales by quarters using the DATE_PART function:

SELECT sale_date,

       CASE

           WHEN DATE_PART('quarter', sale_date) = 1 THEN 'Q1'

           WHEN DATE_PART('quarter', sale_date) = 2 THEN 'Q2'

           WHEN DATE_PART('quarter', sale_date) = 3 THEN 'Q3'

           ELSE 'Q4'

       END AS quarter

FROM sales;

Mathematical Functions

The flexibility of CASE isn’t just limited to strings or dates. Consider a scenario where you wish to give a discount based on the purchase amount:

SELECT purchase_amount,

       purchase_amount -

       CASE

           WHEN purchase_amount > 1000 THEN purchase_amount * 0.1

           ELSE 0

       END AS discounted_amount

FROM orders;

This query gives a 10% discount for orders above $1000.

Nested CASE Expressions

There might be situations when a simple CASE expression won’t suffice. Here, nested CASE expressions come into play, allowing you to evaluate conditions within conditions.

Query Demonstration

To illustrate, let’s categorize products by their price and stock levels:

1SELECT product_name, price, stock,

2       CASE

3           WHEN price > 100 THEN

4               CASE

5                   WHEN stock < 10 THEN 'Expensive, Low Stock'

6                   ELSE 'Expensive, Adequate Stock'

7               END

8           ELSE

9               CASE

10                   WHEN stock < 10 THEN 'Affordable, Low Stock'

11                   ELSE 'Affordable, Adequate Stock'

12               END

13       END AS product_status

14FROM products;

CASE with JOIN Operations

The power of CASE isn’t confined to single tables. You can use it within JOIN operations to generate more meaningful insights.

Query Examples and Results

Consider two tables: orders and products. To list products and classify orders based on their total sale:

SELECT p.product_name,

       CASE

           WHEN SUM(o.order_amount) > 10000 THEN 'High Selling'

           ELSE 'Low Selling'

       END AS sales_status

FROM orders o

JOIN products p ON o.product_id = p.id

GROUP BY p.product_name;

Common Pitfalls and Best Practices

While CASE is potent, it’s not without challenges. Here are some pitfalls to watch out for and best practices to adopt:

Null Values

Remember that NULL values can be tricky with CASE. Always handle them explicitly to avoid unexpected results:

CASE

    WHEN column IS NULL THEN 'Unknown'

    ELSE column

END

Data Type Consistency

Ensure that all branches of your CASE expression return values of the same or compatible data types. Otherwise, PostgreSQL might throw an error.

Performance Considerations

Overuse of CASE, especially in nested scenarios or large datasets, can impact query performance. It’s a good practice to monitor query execution times and optimize as necessary.

Advanced Use Cases

Dynamic Pivot Tables

Pivot tables, though not natively supported in PostgreSQL, can be mimicked using CASE. For instance, to pivot sales data by months:

SELECT product,

       SUM(CASE WHEN DATE_PART('month', sale_date) = 1 THEN sale_amount ELSE 0 END) AS Jan,

       SUM(CASE WHEN DATE_PART('month', sale_date) = 2 THEN sale_amount ELSE 0 END) AS Feb,

       ...

FROM sales

GROUP BY product;

Conditional Aggregation

Aggregate functions can be paired with CASE for conditional logic:

SELECT product,

       SUM(CASE WHEN region = 'North' THEN sale_amount ELSE 0 END) AS North_Sales,

       SUM(CASE WHEN region = 'South' THEN sale_amount ELSE 0 END) AS South_Sales

FROM sales

GROUP BY product;

Creating Dynamic Columns

With CASE, you can conditionally produce columns based on data values. For instance:

SELECT username,

       CASE WHEN login_count > 10 THEN 'Active User' ELSE NULL END AS Active,

       CASE WHEN login_count <= 10 THEN 'Inactive User' ELSE NULL END AS Inactive

FROM users;

Alternatives to CASE

COALESCE Function

COALESCE is a handy function in PostgreSQL that returns the first non-NULL value in the list:

SELECT COALESCE(column1, column2, 'default_value') FROM table_name;

It’s particularly useful as an alternative to CASE when checking for NULL values.

NULLIF Function

NULLIF returns NULL if its two arguments are equal, otherwise, it returns the first argument:

SELECT NULLIF(column, 'value_to_compare') FROM table_name;

It can serve as a simpler alternative to CASE in some scenarios.

Conclusion

The CASE expression is an indispensable tool in the PostgreSQL arsenal, offering conditional logic right within SQL. Its adaptability with other functions, and its applications from basic to advanced scenarios, highlights its significance. Embrace it, but remember to be mindful of the best practices to harness its full potential.

Introduction

Basic Structure of CASE

Simple CASE

Searched CASE

Simple CASE Example

Query Demonstration

Searched CASE Example

Query Demonstration

Using CASE in Different Parts of a Query

In the SELECT Clause

In the WHERE Clause

In the ORDER BY Clause

In the GROUP BY Clause

With Aggregate Functions

Combining CASE with Other SQL Functions

String Functions

Date Functions

Mathematical Functions

Nested CASE Expressions

Query Demonstration

CASE with JOIN Operations

Query Examples and Results

Common Pitfalls and Best Practices

Null Values

Data Type Consistency

Performance Considerations

Advanced Use Cases

Dynamic Pivot Tables

Conditional Aggregation

Creating Dynamic Columns

Alternatives to CASE

COALESCE Function

NULLIF Function

Conclusion

No comments so far