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 isactive
, and then press CTRL + C to exit.Be sure that you also have the PostgreSQL command-line interface
psql
installed. You can run the commandpsql -V
to verify thatpsql
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:
1 | 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:
1 | 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:
1 | CREATE TABLE TABLE_NAME(column_name + [DATA TYPE] + CONSTRAINTS [OPTIONAL]); |
In our example, we’ll use this statement to create a table named employee
:
1 2 3 4 5 6 7 8 9 | 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:
1 | 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:
1 2 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 | 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:
1 2 3 | 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:
1 2 3 | 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:
1 2 3 4 | 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;
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:
1 2 3 | 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