How to Use the FILTER Clause in PostgreSQL

Introduction

If you’re using PostgreSQL version 9.4 or newer, you’ll want to become familiar with the FILTER clause. This clause was designed to be a simpler, more intuitive replacement for the CASE WHEN clause found in earlier versions of PostgreSQL. It can be used to aggregate data in PostgreSQL based on certain criteria. In this article, we’ll take a closer look at the FILTER clause in PostgreSQL and discuss some examples of its use.

Prerequisites

We’ll be looking at some PostgreSQL examples in this tutorial, so it’s important to make sure the following prerequisites are in place:

• Be sure that you have PostgreSQL installed and running on your device. Use the command service postgresql status to find out if the status is active, and then press CTRL + C to exit.

• Be sure that you also have the PostgreSQL command-line interface psql installed. You can run the command psql -V to verify that psql is installed and working properly.

Accessing PostgreSQL using ‘psql’

Now that we’ve reviewed the prerequisites, we can go ahead and access PostgreSQL. We’ll use the following commands to access a PostgreSQL database on our local machine:

This command will prompt you for the user’s password. Once you enter it and press RETURN, you’ll be able to access PostgreSQL.

Use the following command to enter and access a specific database:

You’ll be prompted for the user’s password again. Once again, simply enter it and press RETURN.

Create a PostgreSQL Table in ‘psql’

The next thing we’ll do is create a PostgreSQL table. We can accomplish this using a SQL statement with the following syntax:

In our example, we’ll use this statement to create a table named employee:

Inserting Records into the PostgreSQL Table

Next, we’ll need to insert records into our table. The syntax used to insert a record into a PostgreSQL table is shown below:

We’ll use an INSERT statement to add a record to the employee table:

For the purposes of this tutorial, let’s assume that we’ve inserted a total of 10 records to the employee table. You’d see output that looks like this:

Using the FILTER Clause in PostgreSQL to Execute Functions

A key benefit of the FILTER clause is that it’s more readable than the CASE WHEN clause when used to aggregate queries. In the next section, we’ll provide some examples of using FILTER to execute aggregate functions or stored procedures.

Using SQL Keywords with the FILTER Clause in PostgreSQL

Let’s look at some other SQL clauses we can use in conjunction with the FILTER clause in PostgreSQL:

AS — We use this keyword to create an alias, or correlated name, for a table or query results set.

ANY and ALL — We use these keywords in conjunction with WHERE to filter records meeting specific criteria.

FROM — We use the FROM keyword to specify which table to query with the FILTER clause.

Using the ‘COUNT()’ Function to Filter the Number of Records that Match a Query

The COUNT() function returns just the number of records in the table that match the specified filter. The basic syntax of COUNT() used in conjunction with the FILTER clause in PostgreSQL is shown below:

Notice that the AS keyword is used to assign a name to the column containing the count for the results. Here’s an example of a SQL statement in psql using the COUNT keyword:

In the first column, PostgreSQL would return the results of the COUNT query without the FILTER clause; the second column will include only the records that have an ID value less than 6.

The results of this query should contain the following values:

NOTE: If you’d like to return the actual records and not just the number of records that match your filter, you’ll want to use the SELECT statement in conjunction with WHERE instead. An example of that would look like this: SELECT * FROM some_table WHERE id < 6;

Using ‘CASE WHEN’ instead of the ‘FILTER’ Clause in PostgreSQL

If you’re using a version of PostgreSQL that’s older than 9.4, you’ll need to use the CASE WHEN clause instead of FILTER. You can get the same COUNT() results using this clause, as shown in the example below:

Conclusion

When you’re querying data stored in a PostgreSQL database, there will be times when you want to retrieve a count of records that meet a specified set of criteria. For users of PostgreSQL version 9.4 and above, the FILTER clause makes this task a simple one. In this article, we reviewed some examples of the FILTER clause in PostgreSQL. With this tutorial to guide you, you’ll be able to write filtered queries of your own to understand your data better and get the answers you need.