How to Write a CASE Statement in PostgreSQL

The CASE statement in PostgreSQL lets you add conditional logic to your SQL queries, making them more dynamic and powerful. In this guide, we’ll walk you through how to write and use CASE statements with clear examples.

What is a CASE Statement?

A CASE statement works like an IF-THEN-ELSE block inside your SQL query. It allows you to return specific values based on conditions you define, all within a single query.

Basic Syntax

SELECT 
    column1,
    CASE 
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result_default
    END AS new_column
FROM table_name;
                                

Example: Categorize Orders by Amount

SELECT 
    order_id,
    amount,
    CASE 
        WHEN amount < 100 THEN 'Small'
        WHEN amount BETWEEN 100 AND 500 THEN 'Medium'
        ELSE 'Large'
    END AS order_category
FROM orders;
                                

This query will add an order_category column that classifies orders as Small, Medium, or Large based on their amount.

Using CASE in WHERE Clauses

While you can’t directly put a CASE in the WHERE clause, you can use it inside the conditions:

SELECT *
FROM orders
WHERE 
    (CASE 
        WHEN customer_status = 'VIP' THEN amount > 500
        ELSE amount > 1000
    END);
                                

Best Practices

• Keep conditions clear and avoid overlapping ranges.
• Use ELSE to handle unexpected cases.
• Test your CASE logic with small datasets before deploying.

Summary

The PostgreSQL CASE statement is a versatile tool for adding conditional logic to your queries. Whether you’re categorizing data, transforming values, or customizing output, mastering CASE will help you write smarter, more efficient SQL.