PostgreSQL CASE WHEN: Complete Guide with Examples
PostgreSQL CASE WHEN: Conditional Logic in Queries
The CASE WHEN expression in PostgreSQL provides conditional logic within SQL queries. This guide covers syntax, usage examples, and practical applications.
What is CASE WHEN in PostgreSQL?
The CASE WHEN expression is used to implement conditional logic in SQL queries. It evaluates conditions and returns specific results based on whether the condition is true or false. It's commonly used in SELECT statements, but can also be used in WHERE, ORDER BY, and other SQL clauses.
Syntax:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END
Explanation:
- WHEN: Specifies a condition to evaluate.
- THEN: Defines the result if the condition is true.
- ELSE: (Optional) Specifies a default result if no conditions are met.
- END: Marks the end of the CASE expression.
Example 1: Basic Usage in a SELECT Statement
Code:
-- Select employees and categorize their salaries
SELECT
employee_name,
salary,
CASE
WHEN salary > 70000 THEN 'High'
WHEN salary BETWEEN 50000 AND 70000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;
Explanation:
- Evaluates each employee's salary and categorizes it as 'High', 'Medium', or 'Low'.
- The salary_category column stores the result.
Example 2: Using CASE WHEN in a WHERE Clause
Code:
-- Filter employees based on conditional logic
SELECT
employee_name,
department
FROM employees
WHERE
CASE
WHEN department = 'Sales' THEN 1
ELSE 0
END = 1;
Explanation:
- Only rows where the department is 'Sales' are included in the result.
Example 3: Using CASE WHEN in an UPDATE Statement
Code:
-- Update employee bonuses based on their performance
UPDATE employees
SET bonus =
CASE
WHEN performance_rating = 'Excellent' THEN 1000
WHEN performance_rating = 'Good' THEN 500
ELSE 100
END;
Explanation:
- Updates the bonus field based on employees’ performance ratings.
Example 4: Using CASE WHEN in ORDER BY
Code:
-- Order employees by salary categories
SELECT
employee_name,
salary
FROM employees
ORDER BY
CASE
WHEN salary > 70000 THEN 1
WHEN salary BETWEEN 50000 AND 70000 THEN 2
ELSE 3
END;
Explanation:
- Orders employees by salary category, with 'High' salaries appearing first.
Best Practices
- Use Default Cases: Always include an ELSE clause to handle unexpected inputs.
- Optimize Complex Logic: Combine CASE WHEN with other SQL functions like COALESCE for cleaner queries.
- Avoid Overuse: For extensive conditions, consider refactoring with stored procedures or application-side logic.
Common Errors
- Missing END Keyword: Always conclude the CASE WHEN expression with END.
- Data Type Mismatch: Ensure all THEN and ELSE results have the same data type.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/postgresql-case-when.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics