This post is a quick tip about using the FILTER clause, a feature that was
recently added in version 9.4, to improve some of our old queries that uses
the CASE clause.
What it does?
The filter clause feeds the aggregation function with rows for which it evaluates to true. It literally filter rows according to our query.
Example
Suppose we have two tables in our database like the ones below:
SELECT * FROM sports;
id | name
----+-----------
1 | football
2 | jiu jitsu
3 | parkour
4 | baseball
SELECT * FROM people;
id | name | age | sport_id
----+-------+-----+----------
1 | john | 20 | 1
2 | sam | 21 | 2
3 | matos | 22 | 1
4 | alice | 20 | 1
5 | jean | 28 | 1
6 | bob | 30 | 4
Now we want to count the number of people that likes football (sport_id = 1) grouped by age.
We could do something like this using the good and old CASE clause:
SELECT age, COUNT(CASE WHEN sport_id = 1 THEN 1 ELSE NULL END) AS f
FROM people
GROUP BY age;
age | f
-----+---
28 | 1
30 | 0
20 | 2
22 | 1
21 | 0
(5 rows)
Nothing wrong with this approach, but with postgres 9.4 we can use FILTER
and simplify things a little.
SELECT age, COUNT(*) FILTER(WHERE sport_id = 1) AS f
FROM people
GROUP BY age;
age | f
-----+---
28 | 1
30 | 0
20 | 2
22 | 1
21 | 0
(5 rows)
We are filtering rows that have sport_id = 1 and passing it to COUNT without
worrying about the ones that don’t have it. It just reads better in my opinion.
TL;DR:
Consider using FILTER instead of CASE to improve the query readability.
See you in the next post!