How PostgreSQL's Aggregate FILTER Will Spoil You

Using PostgreSQL will definitely spoil you. I recently had to use another database and found, to my chagrin, that aggregate filters were not included in their implementation of Structured Query Language. Specifically, there was no FILTER clause available.

FILTER?

To illustrate my dependency, let me start with some simple data.

demo=# create table z (a int, b int, c int);
CREATE TABLE
demo=# insert into z values (1,10,100),(2,20,200),(3,30,300),(4,40,400);
INSERT 0 4
demo=# select a,b,c from z;
 a | b  |  c  
---+----+-----
 1 | 10 | 100
 2 | 20 | 200
 3 | 30 | 300
 4 | 40 | 400
(4 rows)
demo=# 

If I wanted the overall number of rows, I could use a simple COUNT(*). If I wanted an overall number of rows where one of the columns was of a certain value, I could add the logic for getting those rows to the WHERE clause.

What if I wanted both?  Do I need two queries to do the work separately? Maybe a materialized view or a CTE (Common Table Expression)? Will I need a Window Function? 

FILTER!


PostgreSQL provides the ability to do what is needed with one SELECT statement. 

demo=# select count(*) as all_rows,
demo-#        count(*) FILTER (where b > 11) as b_gt_11
demo-# from z;
 all_rows | b_gt_11 
----------+---------
        4 |       3
(1 row)

demo=# 

The above SELECT statement asks for two columns of data. The first asks for a count of all rows. 

The second requests a count of all rows where the b column's value exceeds 11.  The FILTER defines the rows of interest. 

Long-time SQL writers will immediately spot how handy the ability to filter rows this way is. 

Sadly, the database I had to use did not offer the ability to do this.  What should have been a simple query became a detour into the bumpy backroads of finding another way to get the answer. 

Comments

Post a Comment

Popular posts from this blog

How Does PostgreSQL Store Your Data?

PostgreSQL is an amazing database. And some of us wonder how it takes what we enter on a keyboard and stores it. The following is peeking into the 'clockworks' to see how things are done. Step 1 On a fresh install of Ubuntu with PostgreSQL 17, let us create a database. stoker@ThinkPad:~$ createdb demo Password:  stoker@ThinkPad:~$ psql demo Password for user stoker:  psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1)) Type "help" for help. Now that we are in this new database, we can ask where PostgreSQL stores the data by looking for the data directory. demo=# show data_directory;        data_directory         -----------------------------  /var/lib/postgresql/17/main (1 row) This informs us that our data will be somewhere under the /var/lib/postgresql17/main directory.  Step 2 Issuing a command to create a table starts of series of events that we will study in later posts. Right now, we want to see where the stuff goes. Here a simp...
Read more

Now That We Know Where PostgreSQL Stores Data, We Can Look At How

 Last time , we started looking at how PostgreSQL stores data by discovering where the data is stored and had a quick peek at that data. There is only one record at this point. demo=# SELECT * FROM ex01;  a | b  ---+---  1 | 1 (1 row) And the contents of that file are not precisely illuminating at first glance. For now, just trust me that our row is in there, and you will see where in just a little bit. root@ThinkPad:/var/lib/postgresql/17/main/base/16389# od -c 16390 0000000  \0  \0  \0  \0   (   z 227 001  \0  \0  \0  \0 034  \0 340 037 0000020  \0     004      \0  \0  \0  \0 340 237   @  \0  \0  \0  \0  \0 0000040  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0 * 0017740 362 002  \0  \0  \0  \0  \...
Read more