The FILTER clause in Postgres 9.4

Anderson Dias
Dec 30, 2015 · 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 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_id

The 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!


Little programming joys

Every day we learn something little, something amazing.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store