PostgreSQL – CASE Statement

The CASE statement in PostgreSQL is used to perform conditional logic within a query. It allows you to create conditional expressions that produce different results based on specified conditions. There are two forms of the CASE statement in PostgreSQL: the simple CASE and the searched CASE.

1. Simple CASE: In the simple CASE form, you compare a single expression (often a column value) against a set of values. When a match is found, a corresponding result is returned.

SELECT column_name, 
CASE column_name 
WHEN value1 THEN result1 
WHEN value2 THEN result2 
ELSE result_default 
END 
FROM table_name;

Example:

SELECT name, 
CASE department_id 
WHEN 1 THEN 'HR' 
WHEN 2 THEN 'Finance' 
WHEN 3 THEN 'IT' 
ELSE 'Unknown' 
END AS department 
FROM employees;

2. Searched CASE: In the searched CASE form, you evaluate different conditions for each row and return the corresponding result.

SELECT column_name, 
CASE 
WHEN condition1 THEN result1 
WHEN condition2 THEN result2 
ELSE result_default 
END 
FROM table_name;

Example:

SELECT name, 
CASE 
WHEN salary > 50000 THEN 'High' 
WHEN salary > 30000 THEN 'Medium' 
ELSE 'Low' 
END AS salary_range 
FROM employees;

The CASE statement is a powerful tool for performing conditional transformations and computations within your SQL queries. It can be used in various parts of a query, including the SELECT clause, WHERE clause,etc.

While CASE expressions are quite flexible, you should consider using PL/pgSQL (PostgreSQL’s procedural language) for more advanced control flow within functions or procedures if you find yourself dealing with more complex logic.