A simple example from Postgres offical documents:
SELECT * FROM test; a --- 1 2 3 SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test; a | case ---+------- 1 | one 2 | two 3 | other
It is based on this form:
CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
It is very convenient in aggregation functions, just think table foo below:
id is table’s primary key, sex is 0 or 1, it stands for male and female.
Now, how can we lookup how many males and females at the same time ?
SELECT sum(CASE WHEN sex=0 THEN 1 ELSE 0 END) AS total_male, sum(CASE WHEN sex=1 THEN 1 ELSE 0 END) AS total_female FROM foo;