How to Use Filter in PostgreSQL with example

Inroduction

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-WHEN in Postgres

Since CASE is an expression, you can use it in any places where an expression can be used e.g.,SELECT, WHERE, 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;

The FILTER clause in Postgres 9.4

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     |             |              |            |             |           |
...

Conclusion

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.

References:

• the-filter-clause-in-postgres
• sql-expressions