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:
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:
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:
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:
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:
Best Practices
Common Errors
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4