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.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

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:

sudo su - postgres

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:

psql some_username -h 127.0.0.1 -d some_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:

CREATE TABLE TABLE_NAME(column_name + [DATA TYPE] + CONSTRAINTS [OPTIONAL]);

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

CREATE TABLE employee(
ID SERIAL PRIMARY KEY,
name TEXT,
address TEXT,
country TEXT,
phone TEXT,
email VARCHAR(50),
website TEXT
);

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:

INSERT INTO TABLE_NAME(column1, column2, column3) VALUES(values1, values2, values3);

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

INSERT INTO employee(name, address, country, phone, email, website)
VALUES('Blair', '209 Decker Dr', 'Philadelphia', '215-907-9111', 'bmalet@yahoo.com', 'http://www.bollingermachshpshipyard.com');

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:

id | name | address | country | phone | email | website
----+---------+----------------------------+--------------+--------------+-----------------------------+-----------------------------------------
1 | Blair | 209 Decker Dr | Philadelphia | 215-907-9111 | bmalet@yahoo.com | http://www.bollingermachshpshipyard.com
2 | Brock | 4486 W O St #1 | NEW York | 212-402-9216 | bbolognia@yahoo.com | http://www.orindanews.com
3 | Lorrie | 39 S 7th St | Coffee | 931-875-6644 | lnestle@hotmail.com | http://www.ballardspahrandrews.com
4 | Sabra | 98839 Hawthorne Blvd #6101 | Richland | 803-925-5213 | sabra@uyetake.org | http://www.lowylimousineservice.com
5 | Marjory | 71 San Mateo Ave | Delaware | 610-814-5533 | mmastella@mastella.com | http://www.viconcorporation.com
6 | Karl | 76 Brooks St #9 | Hunterdon | 908-877-6135 | karl_klonowski@yahoo.com | http://www.rossimichaelm.com
7 | Tonette | 4545 Courthouse Rd | Nassau | 516-968-6051 | twenner@aol.com | http://www.northwestpublishing.com
8 | Amber | 14288 Foster Ave #4121 | Montgomery | 215-934-8655 | amber_monarrez@monarrez.org | http://www.branfordwiremfgco.com
9 | Shenika | 4 Otis St | Los Angeles | 818-423-4007 | shenika@gmail.com | http://www.eastcoastmarketing.com
10 | Delmy | 65895 S 16th St | Providence | 401-458-2547 | delmy.ahle@hotmail.com | http://www.wyetechnologiesinc.com

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:

SELECT COUNT(*) AS [column_name],
COUNT(*) FILTER (WHERE {column_name}{comparison_operator}{VALUE} ) AS [column_name]
FROM TABLE_NAME;

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:

SELECT COUNT(*) AS no_filter,
COUNT(*) FILTER (WHERE id < 6 ) AS with_filter
FROM employee;

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:

no_filter | with_filter
-----------+-------------
10 | 5
(1 ROW)

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;

Screenshot of psql using FILTER and SELECT to query a PostgreSQL table

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:

SELECT COUNT(*) AS no_filter,
COUNT(CASE WHEN id < 3 THEN 1 END) AS with_filter
FROM some_table;

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.