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_result that 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.