Mastering PostgreSQL 9.6

Using FILTER clause

When looking at the SQL standard itself, you will notice that the FILTER clause has already been around with SQL (2003). However, not many systems actually support this highly useful syntax element.

Here is an example:

test=# SELECT  count(*),                 count(*) FILTER (WHERE id < 5),                 count(*) FILTER (WHERE id > 2)         FROM    generate_series(1, 10) AS id;  count | count | count  -------+-------+-------     10 |     4 |     8 (1 row) 

The FILTER clause is useful if a condition cannot be used inside a normal WHERE clause because some other aggregate is in need of the data.

Before the introduction of the FILTER clause, the same could be achieved using a more cumbersome syntax:

SELECT sum(CASE WHEN .. THEN 1 ELSE 0 END) AS whatever  FROM some_table; ...