Postgres FILTER Clause: Conditional Aggregates Without Subqueries

Counting how many users signed up this month, how many active, how many churned, and how many on each plan is one of the most common analytical queries any SaaS team writes. The textbook answer involves either multiple separate queries with different WHERE clauses, or one query with a stack of SUM(CASE WHEN ... THEN 1 ELSE 0 END) expressions. Both work. Both are uglier than they need to be. The FILTER clause, added to standard SQL in 2003 and supported in Postgres since version 9.4 in 2014, makes the same query shorter, faster, and easier to read.

The shape of the problem

A typical conditional-aggregate question looks like this: for the customers table, give me the total count, the count of paying customers, the count of customers who signed up in the last 30 days, and the count of customers on each plan tier. Three or four conditional counts in a single result row.

The naive approach runs four separate queries. The CTE approach unions them. The CASE WHEN approach does them in one query but reads as a noisy stack:

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN plan != 'free' THEN 1 ELSE 0 END) AS paying,
  SUM(CASE WHEN created_at > NOW() - INTERVAL '30 days' THEN 1 ELSE 0 END) AS new_30d,
  SUM(CASE WHEN plan = 'starter' THEN 1 ELSE 0 END) AS starter,
  SUM(CASE WHEN plan = 'pro' THEN 1 ELSE 0 END) AS pro,
  SUM(CASE WHEN plan = 'business' THEN 1 ELSE 0 END) AS business
FROM customers;

The query works. It runs in a single table scan. It is also nine lines of CASE WHEN ... THEN 1 ELSE 0 END wrapped in SUM, which is more punctuation than logic. Every reviewer has to mentally translate "SUM of a 1-or-0 expression" back to "count where condition."

The FILTER clause

The FILTER clause attaches a WHERE condition to a single aggregate. The same query becomes:

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE plan != 'free') AS paying,
  COUNT(*) FILTER (WHERE created_at > NOW() - INTERVAL '30 days') AS new_30d,
  COUNT(*) FILTER (WHERE plan = 'starter') AS starter,
  COUNT(*) FILTER (WHERE plan = 'pro') AS pro,
  COUNT(*) FILTER (WHERE plan = 'business') AS business
FROM customers;

The intent is now legible at first read: count rows; count rows where plan is not free; count rows in the last 30 days; count rows on each tier. The structure mirrors the question rather than translating it through a workaround.

FILTER works on any aggregate, not just COUNT. SUM(amount) FILTER (WHERE refunded = false) gives revenue excluding refunds. AVG(latency_ms) FILTER (WHERE endpoint = '/api/v1/charge') gives the average latency of a specific endpoint within a broader scan. STRING_AGG(name, ', ') FILTER (WHERE role = 'admin') lists the admins in a group. The clause attaches to the aggregate, not to the query, so different aggregates in the same SELECT can have different filters.

What the planner does with FILTER

FILTER produces the same query plan as the equivalent CASE WHEN expression. Both compile down to a single sequential scan (or index scan, if a useful index exists) that evaluates each predicate per row and increments the matching aggregate counters. There is no performance penalty for using FILTER over CASE WHEN, and no performance benefit. The case for FILTER is purely about readability.

Where FILTER does sometimes win is when the planner can push the FILTER predicate into the scan. If the FILTER condition references an indexed column and the rest of the query allows it, the planner can use the index to skip rows that do not match, the same way it would for a WHERE clause. This is an EXPLAIN-visible optimization; checking the plan is the way to confirm.

The aggregate-with-FILTER pattern also composes with GROUP BY in the obvious way. SELECT plan, COUNT(*) FILTER (WHERE last_login > NOW() - INTERVAL '7 days') AS active_users FROM customers GROUP BY plan gives a per-plan active-user count without nested subqueries.

Where FILTER does not help

FILTER is a row-level filter applied to a single aggregate. It does not help when the question requires aggregating different groupings in the same query; that needs GROUPING SETS or ROLLUP. It does not help when the aggregate needs to be computed across joins with different row counts; that needs CTEs or window functions. It does not change the underlying scan cost; if the table is large and the query scans the whole thing, FILTER does not save reads.

FILTER also does not work in some places where you might expect it to. It cannot be combined with DISTINCT in standard SQL (COUNT(DISTINCT customer_id) FILTER (WHERE ...) works in Postgres but is technically a Postgres extension). It cannot be used inside window functions in older Postgres versions (Postgres 16 added support, earlier versions reject it).

The migration path

Existing queries with CASE WHEN aggregates can be rewritten to FILTER mechanically. The translation is one-to-one: every SUM(CASE WHEN cond THEN 1 ELSE 0 END) becomes COUNT(*) FILTER (WHERE cond), and every SUM(CASE WHEN cond THEN x ELSE 0 END) becomes SUM(x) FILTER (WHERE cond). The query plan should be identical; the test is to run both versions and compare EXPLAIN output.

The case for retrofitting old queries is weaker than the case for using FILTER in new code. The CASE WHEN form is correct and works; the cost of an in-place rewrite is review time and the risk of typos. The high-value places to switch are queries that get edited often, queries that confuse newcomers, and queries whose growing list of conditional aggregates is making the WHEN stack unmanageable.

What this looks like across our four products

Our four products (DocuMint for PDF invoice generation, CronPing for cron job monitoring, FlagBit for feature flags, and WebhookVault for webhook debugging) are SQLite-based at current scale. SQLite added FILTER clause support in version 3.30 in 2019, so the pattern transfers across both databases without modification. The internal dashboards that aggregate per-tenant usage data use FILTER for the per-plan, per-status, and per-time-window breakdowns; the analytics views are about a third shorter than they would be with the CASE WHEN equivalent.

The places we still use CASE WHEN are the ones where the conditional logic returns something other than 1 or 0 inside an aggregate — for instance, computing a weighted average where the weight depends on a category. FILTER does not help there, because the conditional is on the contributing value, not on row membership.

Three observations

First: FILTER has been standard SQL since 2003 and supported in Postgres since 2014. The pattern of teams not knowing about it in 2026 is a reminder that SQL features diffuse slowly through the working developer population. There are likely several more features in the standard that would be helpful and that most teams do not know about; the SQL standard documentation is dense and rarely read end-to-end, and feature blog posts get lost in the noise.

Second: the FILTER vs CASE WHEN choice is one of the cleanest examples of "the same operation expressed two ways, where one expresses the intent and the other expresses the workaround." The query plans are identical; the readability difference is large. The choice between them is about whether the team values code that matches the mental model or accepts code that matches the historical pattern.

Third: small ergonomic features compound over years. FILTER saves a few seconds of reading per query, but most analytics dashboards in mature systems have dozens of these queries, and they get edited and reviewed many times over their lifetime. The cumulative cost of unnecessary noise is large; the cumulative cost of teaching one feature is small.

The deeper observation is that the SQL ecosystem has a long tail of useful features that have been standard for years and are not yet universal in working code. FILTER is one of them. WINDOW FUNCTION variants like LAG, LEAD, FIRST_VALUE, LAST_VALUE are others. GROUPING SETS and CUBE and ROLLUP are others still. Each of them is documented, each of them ships in every modern database, and each of them is used by a fraction of the teams that would benefit from it. The diffusion of database features through the developer population is slower than the diffusion of programming language features through the same population, partly because SQL is often learned once and not revisited, and partly because the language tools (linters, formatters, autocomplete) for SQL are weaker than the equivalent tools for general-purpose languages. Closing that gap would be one of the higher-leverage improvements in developer tooling, and would compound across the working life of every team that writes nontrivial queries.