How to Use Filter in PostgreSQL with example
Milind Soorya / September 06, 2021
2 min read
Postgres 9.4 was released in December 2014 adding the
FILTER clause to aggregate functions and this clause is quite useful when you want to count or sum specific records when executing group by.
Until Postgres 9.4 release, if you wanted to count a few set of records when executing an aggregate function, you had to use a
CASE WHEN. The PostgreSQL
CASE expression is the same as
IF/ELSE statement in other programming languages. It allows you to add if-else logic to the query to form a powerful query.
CASE is an expression, you can use it in any places where an expression can be used e.g.,
GROUP BY, and
HAVING clause. you can use a
CASE WHEN as shown in the sample below:
SELECT title, length, CASE WHEN length> 0 AND length <= 50 THEN 'Short' WHEN length > 50 AND length <= 120 THEN 'Medium' WHEN length> 120 THEN 'Long' END duration FROM film ORDER BY title;
Since 9.4 release we can replace the CASE WHEN clauses in these aggregate functions by the new FILTER clause:
SELECT EXTRACT(MONTH FROM payment_date) AS month, COUNT(*) AS total_count, SUM(amount) AS total_amount, COUNT(*) FILTER (WHERE staff_id = 1) AS mike_count, SUM(amount) FILTER (WHERE staff_id = 1) AS mike_amount, COUNT(*) FILTER (WHERE staff_id = 2) AS jon_count, SUM(amount) FILTER (WHERE staff_id = 2) AS jon_amount FROM payment GROUP BY month ORDER BY month;
The above query is made on the DVD Rental Sample Database and is used to produce a result set for the report that shows a side-by-side comparison of the number and total amounts of payments made in Mike's and Jon's stores broken down by months.
month | total_count | total_amount | mike_count | mike_amount | jon_count | jon_amount ------+-------------+--------------+------------+-------------+-----------+----------- 2 | | | | | | 5 | | | | | | ...
FILTER clause provides a better way to deal with scoped aggregate functions and it’s a beautiful way to replace the
CASE WHEN statements for these cases.