PostgreSQL CASE Expressions: If-else in Select Query
PostgreSQL supports CASE expression which is the same as if/else statements of other programming languages.
The CASE expression can be used with SELECT, WHERE, GROUP BY, and HAVING clauses.
CASE WHEN <condition1> THEN <result1> WHEN <condition2> THEN <result2> …. [ELSE <else_result>] END
In the above syntax, every condition is a boolean expression that evaluates to be either true or false. The CASE expression evaluates a list of conditions in sequence.
- If the condition evaluates to true, the CASE expression will return the corresponding result set for that condition and stop evaluating the next expression.
- If the condition evaluates to false, the CASE expression keeps on evaluating the next condition until it finds the expression to be evaluated as true.
If of all the conditions evaluates to be false, then it returns
else_resultthat is in the ELSE clause. The ELSE clause is optional. If the ELSE clause is not defined for CASE expression, then it will return NULL.
The data types of all the result expressions must be convertible to a single output type, otherwise CASE expression will raise error.
Let's use the following Employee table to understand the CASE expression.
Simple CASE Expression
The following query is using simple CASE expression, where it checks the value of the column and returns the resultset as per value.
SELECT emp_id, first_name, last_name, CASE gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END gender FROM Employee;
Note that in the above CASE expression, the ELSE case is not specified, so for
emp_id = 4, it shows gender as null.
General CASE Expression with ELSE
Let's use the CASE expression to do a salary analysis of employees where salary will be categorized as Low, Average, Very Good, and No Data, based on the following range:
- 'Low' if salary is < 50000
- 'Average' if salary > 50000 AND salary <= 100000
- 'High' if salary > 100000
- 'No Data' if salary is null
SELECT emp_id, first_name, last_name, salary, CASE WHEN salary <= 50000 THEN 'Low' WHEN salary > 50000 AND salary <= 100000 THEN 'Average' WHEN salary > 100000 THEN 'High' ELSE 'No Data' END salary_analysis FROM Employee;
In the above query, the CASE expression is evaluated on the
Salary column of the
Employee table. Note that we use column alias
salary_analysis as CASE expression column.