PostgreSQL CASE Multiple Columns

In this PostgreSQL tutorial, we will learn about PostgreSQL CASE Multiple Columns with different examples such as using the simple and searched CASE.

What are PostgreSQL CASE Multiple Columns?

Similar to IF-THEN-ELSE statements in other programming languages, the CASE statement in PostgreSQL expresses a condition. It allows us to conditional login in SQL queries, which makes them more flexible and dynamic.

There are two forms of the CASE statement in PostgreSQL:

Simple CASE: This form calculates the result by comparing an expression to a collection of simple expressions.

CASE expression 
    WHEN value1 THEN result1 
    WHEN value2 THEN result2 
    ...
    ELSE result 
END

Searched CASE: To determine the result, this form uses independent Boolean expressions.

CASE 
    WHEN Boolean_expression1 THEN result1
    WHEN Boolean_expression2 THEN result2
    ...
    ELSE result 
END

The “ELSE” clause is optional.

PostgreSQL CASE Multiple Columns

The CASE statement can be used when working with multiple columns to apply it to each column separately or to make new categories or flags based on the information from other columns.

Suppose, we have a customers table with the following columns id, first_name, last_name, country, account_status, and purchase_history.

PostgreSQL CASE Multiple Columns Simple CASE

We will use the simple CASE statement to classify customers based on their country and account_status.

SELECT 
    id, 
    first_name, 
    last_name, 
    country,
    account_status,
    CASE 
        WHEN country = 'USA' AND account_status = 'active' THEN 'Active USA Customer'
        WHEN country = 'USA' AND account_status = 'inactive' THEN 'Inactive USA Customer'
        WHEN country <> 'USA' AND account_status = 'active' THEN 'Active Non-USA Customer'
        ELSE 'Others'
    END as customer_category 
FROM 
    customers;

The above query retrieves data from the customers table, returning the id, first_name, last_name, country, and account_status for each record.

Based on the combination of their country and account_status values, customers are classified by the CASE statement as “Active USA Customer”, “Inactive USA Customer”, and “Active Non-USA Customer”.

A new column “customer_category” is created in the returned data as a result of the CASE statement.

Read Also:How to Setup Oracle Database In Archivelog Mode

PostgreSQL CASE Multiple Columns Searched CASE

We will use the searched CASE statement that categorizes customers according to their ‘purchase_history’.

SELECT 
    id, 
    first_name, 
    last_name, 
    purchase_history,
    CASE 
        WHEN purchase_history >= 100 THEN 'Premium Customer'
        WHEN purchase_history >= 50 AND purchase_history < 100 THEN 'Regular Customer'
        WHEN purchase_history < 50 THEN 'New Customer'
        ELSE 'Uncategorized'
    END as customer_type 
FROM 
    customers;

The above query gets data from the customers table and makes a new column, customer_type based on the purchase_history value.

Customers are classified as “Premium Customer” if their purchase_history totals 100 or higher, and “Regular Customer” if their purchase_history is between 50 (inclusive) and 100 (exclusive). if none of these conditions are met, the customer is marked as “Uncategorized”.

Conclusion

In this PostgreSQL tutorial, we covered how to use the CASE with multiple columns and learned about the two types of CASE, the simple CASE, and searched CASE with examples.

You may like to read:

• What is PostgreSQL [Features & Disadvantages]
• How to use PostgreSQL To_Number
• PostgreSQL Update Limit