The FILTER clause in Postgres 9.4
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 as shown in the sample bellow:
SELECT
COUNT(*) AS unfiltered,
SUM( CASE WHEN i < 5 THEN 1 ELSE 0 END ) AS filtered
FROM generate_series(1,10) AS s(i);It will count every case in unfiltered counter and when i < 5 it will sum 1 to the filtered counter. This is how I’ve coded many times in my reports.
Since 9.4 release we can replace the CASE WHEN clauses in these aggregate functions by the new FILTER clause:
SELECT
COUNT(*) AS unfiltered,
COUNT(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);The result is the same and the syntax is much clearer!
Here is a real world report example.
We need to return sellers sales counters based on purchase states. The report should return the count of all seller sales and a count of successful sales:
SELECT purchases.seller_id,
SUM(CASE WHEN state IN ('authorized', 'reversed') THEN 1 ELSE 0 END) AS sales_count,
SUM(CASE WHEN state = 'authorized' THEN 1 ELSE 0 END) AS successful_sales_count
FROM purchases
GROUP BY purchases.seller_id
Replacing the CASE WHEN clauses by FILTER will generate the following SQL:
SELECT purchases.seller_id,
COUNT(1) FILTER (WHERE state IN ('authorized', 'reversed')) AS sales_count,
COUNT(1) FILTER (WHERE state = 'authorized') AS successful_sales_count
FROM purchases
GROUP BY purchases.seller_idThe 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.
Special thanks to Leonardo Saraiva who gave me this advice and to Thales Oliveira with the review.
Happy coding!
