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.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

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