postgres sql filter

FILTER clause is to be able to run partial aggregates.The FILTER clause allows you to selectively pass data to those aggregates.

 SELECT region,
    avg(production) AS all,
    avg(production) FILTER (WHERE year  < 1990) AS old,
    avg(production) FILTER (WHERE year  >= 1990) AS new
FROM t_oil
GROUP BY ROLLUP (region); 

FILTER works for all kinds of aggregates and offers a simple way to pivot your data. Also, FILTER is faster than mimicking the same behavior with CASE WHEN ... THEN NULL ... ELSE END. You can find some real performance comparisons here: https://www.cybertec-postgresql.com/en/postgresql-9-4-aggregation-filters-they-do-pay-off/.