PostgreSQL Conditional Aggregation

Tip: FILTER Clause

SELECT
    count(*) FILTER (WHERE status = 'published') as published,
    count(*) FILTER (WHERE status = 'draft') as draft,
    count(*) as total
FROM posts;

Gotcha: FILTER vs CASE

count(CASE WHEN status = 'published' THEN 1 END)

FILTER is cleaner and often faster.

Tip: Conditional SUM

SELECT
    sum(amount) FILTER (WHERE type = 'income') as income,
    sum(amount) FILTER (WHERE type = 'expense') as expense
FROM transactions;

Gotcha: Multiple Aggregates

One query can compute many conditional aggregates. No need for multiple queries.

Tip: FILTER with AVG

SELECT avg(rating) FILTER (WHERE rating > 0) FROM reviews;

Excludes zero ratings from the average.

Gotcha: GROUP BY with FILTER

SELECT category_id,
    count(*) FILTER (WHERE published = true) as published_count
FROM posts GROUP BY category_id;

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL's FILTER (WHERE ...) clause for aggregate functions is more readable and often faster than CASE-based conditional aggregation. COUNT(*) FILTER (WHERE status = 'active') is clearer than COUNT(CASE WHEN status = 'active' THEN 1 END). I've migrated all reporting queries to use FILTER syntax — it's standard SQL and PostgreSQL 9.4+ optimizes it better than CASE expressions in aggregates.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)