Understanding FILTER in PostgreSQL (With Examples)

Written by Team Timescale

In PostgreSQL and TimescaleDB, the FILTER clause can be used to extend aggregate functions like sum(), avg(), and count() by adding a WHERE clause. This is especially useful when you want to perform multiple aggregations in your query.

When FILTER is used with an aggregate function, only the input rows that its WHERE clause evaluates to be true will be used in the aggregate clause. The data being processed by the aggregate function is “filtered” by the WHERE clause’s condition.

Syntax:

<aggregate_function>(<expression>) FILTER(WHERE <condition>) Copy

If you use an aggregate function with a window function call, here is the syntax:

<aggregate_function>(<expression>) FILTER(WHERE <condition>) OVER(<window_definition) Copy

A FILTER clause can only be used with window functions that are aggregates.

Examples

Let’s look at some examples to better understand FILTER clauses. For the first set of examples, we’ll use the data in this table called car_sales.

Replacing a CASE Statement With FILTER

PostgreSQL didn’t have FILTER until version 9.4. Before this version, database developers often used a CASE statement and WHEN clauses to get the results you can now get with FILTER. This method was much less straightforward.

Let’s start with a simple query to get the minimum and maximum revenue per car maker. Here is the query to get the results using the FILTER clause:

SELECT min(revenue) FILTER (WHERE make = ‘Ford’) min_ford, max(revenue) FILTER (WHERE make = ‘Ford’) max_ford, min(revenue) FILTER (WHERE make = ‘Renault’) min_renault, max(revenue) FILTER (WHERE make = ‘Renault’) max_renault FROM car_sales;Copy

We created an alias for each of these sums with the AS keyword so that we can differentiate the results, or else each column in the result set would be labeled simply min or max. Here are the results:

To get the same results with a CASE statement, you would have to use this query:

SELECT min(CASE WHEN make = ‘Ford’ THEN revenue ELSE null END) min_ford, max(CASE WHEN make = ‘Ford’ THEN revenue ELSE null END) max_ford, min(CASE WHEN make = ‘Renault’ THEN revenue ELSE null END) min_renault, max(CASE WHEN make = ‘Renault’ THEN revenue ELSE null END) max_renault FROM car_sales;Copy

For cases like this, FILTER is easier to understand.

Pivoting Tables With FILTER

We can use the FILTER clause to pivot rows into tables, which you often need to do to generate reports. Let’s say we want to know the total quantity of cars sold in each month. We could use GROUP BY to do that with a query like this:

SELECT sales_month, sum(quantity) FROM car_sales GROUP BY sales_month;

These are the results:

Or we could use FILTER to pivot these results with a query like this:

SELECT sum(quantity) FILTER (WHERE sales_month = 1) jan_quantity, sum(quantity) FILTER (WHERE sales_month = 2) feb_quantity, sum(quantity) FILTER (WHERE sales_month = 3) mar_quantity, sum(quantity) FILTER (WHERE sales_month = 4) apr_quantity FROM car_sales;Copy

Results:

Using FILTER Clauses With Window Functions

For the next example, we are going to use a different data set that contains temperature and precipitation data from two cities.

In the next query, we are going to get the three-day moving average of the temperature in each city. To do this, we will define a window using the OVER clause and partition it by the city. To show how FILTER works with the window clause, we will only return the maximum temperature when the temperature for any day in a window, including the current row plus the two rows before is over 70 degrees. In other words, it will give us the highest temperature of the last three days, inclusive of any time the temperature for the day is over 70.

Here is the query:

SELECT city, day, temperature,     MAX(temperature)     FILTER (WHERE temperature > 70)     OVER (       PARTITION BY city       ORDER BY day ASC       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) max_3day FROM city_data ORDER BY city, day;Copy

And here are the results:

Next Steps

To learn more about the FILTER clause and how to use it in PostgreSQL, you can check out PostgreSQL’s documentation on aggregate expressions and window function calls.

If you want to experiment with FILTER in a PostgreSQL (but faster) database while enjoying the benefits of automatic data partitioning and columnar compression to speed up performance and reduce storage, create a free Timescale account today.