PostgreSQL CASE: Build Smarter Queries with Conditional Logic

Need to write flexible SQL queries in PostgreSQL? Use the CASE statement to return different results depending on your conditions. Whether you're customizing output, formatting values, or sorting manually, CASE helps you get it done.

PostgreSQL CASE – Full Overview

Simple CASE

Best for comparing one field to multiple values.

SELECT name,
  CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
  END AS status_label
FROM users;

Searched CASE

Great for condition-based evaluation.

SELECT order_id, quantity,
  CASE
    WHEN quantity >= 100 THEN 'High Volume'
    WHEN quantity >= 50 THEN 'Medium Volume'
    ELSE 'Low Volume'
  END AS volume_group
FROM sales;

Where CASE Makes a Difference

• Custom groups for reports
• Replacing NULLs with defaults
• Setting sorting rules
• Dynamic labels in dashboards

Example: conditional grouping

SELECT title,
  CASE
    WHEN rating > 4 THEN 'Top Rated'
    ELSE 'Standard'
  END AS rating_group
FROM books;

FAQ

Can CASE be used with JOINs?

Yes, anywhere you can put an expression.

Is ELSE mandatory?

No, but it’s best to use it to avoid NULLs.

What’s the difference between Simple and Searched?

Simple compares one value; Searched checks full expressions.

Can CASE be used in UPDATE?

Yes — it’s a smart way to apply different updates based on data.

Conclusion

The CASE statement in PostgreSQL helps you build smart SQL that adapts to your data. Once you get the pattern, it’s easy to reuse it for all sorts of logic in your queries.

Check out more examples in the full guide: PostgreSQL CASE: A Comprehensive Guide