3 ms·

I knew my constant browsing of HN would pay off one day. I didn't even know filtered aggregates were a thing and solved a problem I was having just by discoveri

• jbigelow76 12y ago
  Can you explain the difference between a filtered aggregate and an aggregate on a result set that has been limited by a WHERE clause? It's difficult to tell the difference from the annotation on the comparison table.
  • xtrumanx 12y ago
    Well here's how I used it. My query was joining two tables with a left join and I was using the array_agg aggregate function (which aggregates a series of rows into a single array) on a record on the right table. Since the right table could be have rows that were all null I was getting aggregation of null values sometimes which I didn't want. I didn't want to skips rows with null values (which is why I used a left join) since I needed the values of the left table always and thus had a conundrum. With the filtered aggregation, I could tell PostgreSQL to not aggregate the null values. Here's the results of my query before and after using the filtered aggregate. Before: {"name":"asd","id":2,"pages":[{"id":null,"data":null,"siteid":null}]} After: {"name":"asd","id":2,"pages":null}