CASE in PostgreSQL explained with examples

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.

Sharing is caring

Did you like what Mehul Mohan wrote? Thank them for their work by sharing it on social media.

0/10000

No comments so far