PostgreSQL Aggregate Functions

A common mistake is using `count(col)` expecting it to count all rows, then being surprised that NULL values are silently skipped. Equally, using `count(*)` to count non-null values in a specific column is incorrect.

✓ Instead: Use `count(*)` for total row count and `count(col)` explicitly when NULL-exclusion is desired. To count NULLs: `count(*) - count(col)`.

`sum()` returns NULL — not 0 — when there are no rows or all values are NULL. This can propagate silently through calculations and produce unexpected NULL results downstream.

✓ Instead: Always wrap with `COALESCE(sum(col), 0)` when a zero value is semantically correct for empty groups.

When `avg()` is applied to integer columns, PostgreSQL returns a `numeric` type, but many developers cast back to integer with `::int`, silently truncating fractional values (e.g., an average of 3.7 becomes 3).

✓ Instead: Cast to `numeric(10,2)` or `float8` rather than `int` to preserve meaningful decimal precision: `avg(score)::numeric(10,2)`.

A frequent anti-pattern is `SELECT * FROM t WHERE val = (SELECT val FROM t ORDER BY val DESC LIMIT 1)` for each group, which is verbose and may not use indexes as effectively as a simple aggregate.

✓ Instead: Use `max()` with `GROUP BY` for per-group maximums. For fetching the full row of the max, use a window function: `DISTINCT ON` or `RANK() OVER (...)`.

Using `WHERE price = (SELECT min(price) FROM products)` in a subquery is correct but becomes a repeated scan when done per group. It also returns multiple rows on ties without a tiebreaker.

✓ Instead: For per-group minimum rows, prefer `DISTINCT ON (group_col) ORDER BY group_col, price ASC` or a window function with `RANK()` to handle ties explicitly.

Without an explicit `ORDER BY`, `array_agg` produces elements in an undefined, non-deterministic order that can change between query executions, PostgreSQL versions, or after autovacuum.

✓ Instead: Always specify `array_agg(col ORDER BY col)` or an appropriate sort key when the order of array elements matters downstream.

`string_agg` requires exactly two arguments. Passing an empty string `''` as the delimiter will concatenate values without any separator, which is often a mistake when a comma or space was intended.

✓ Instead: Always explicitly pass the desired delimiter. If you truly want no separator, document it clearly. Use `array_agg` + `array_to_string` if you need to switch delimiters later.

`json_agg` returns the `json` type, which cannot be directly queried with operators like `@>`, `?`, or `->>` in a performant way as a stored value.

✓ Instead: Use `jsonb_agg` when the aggregated array will be stored in a JSONB column or queried with JSONB containment and key-existence operators.

Unlike sets, `jsonb_agg` preserves duplicates. If you aggregate from a JOIN that produces duplicate rows (e.g., without DISTINCT), the resulting JSONB array will contain repeated entries silently.

✓ Instead: Use `jsonb_agg(DISTINCT col)` or ensure the source rows are deduplicated before aggregation.

`json_object_agg` does not enforce unique keys. When source rows contain duplicate key values, the resulting JSON object will have repeated keys, which is technically valid JSON but breaks most parsers and libraries.

✓ Instead: Ensure keys are unique before aggregating, or use `jsonb_object_agg` which overwrites duplicate keys with the last value (deterministic with `ORDER BY`).

A common workaround is `HAVING count(*) = count(*) FILTER (WHERE cond)` to verify all rows match a condition. This is hard to read and easy to get wrong.

✓ Instead: Use `bool_and(condition)` for a clear, intent-revealing check that every row satisfies the condition.

Writing a correlated `EXISTS (SELECT 1 FROM t WHERE t.group_id = outer.id AND condition)` for every group in a larger query causes repeated subplan executions.

✓ Instead: Use `bool_or(condition)` inside a `GROUP BY` query for a single-pass, cleaner equivalent.

Testing `WHERE permissions = 7` to check if a user has all three permission bits set conflates exact equality with bit intersection, breaking when other bits are also set.

✓ Instead: Use `bit_and(permissions) & required_mask = required_mask` to verify that specific bits are set across all rows.

Writing `WHERE perms = 1 OR perms = 2 OR perms = 4` instead of leveraging bitmask aggregation becomes unmaintainable as the flag set grows and cannot summarize across multiple roles.

✓ Instead: Store capabilities as bitmasks and use `bit_or(permission_mask)` to compute the effective union in a single aggregate scan.

`stddev` (alias for `stddev_samp`) applies Bessel's correction (divides by N-1), which is appropriate for samples but inflates the value for tiny groups. On a group of 2 rows, the result can be misleading.

✓ Instead: Use `stddev_pop` when working with complete populations (all data, not a sample). Always check `count(*) > 30` before drawing statistical conclusions from `stddev`.

Variance is scale-dependent: doubling measurement units quadruples the variance. Comparing `variance(price_usd)` to `variance(quantity)` is meaningless because the units differ.

✓ Instead: Standardize values (z-score normalize) before comparing spread across different columns, or compare coefficients of variation (`stddev / avg`) instead.

Averages are heavily influenced by outliers. In skewed distributions (e.g., income, response times), the average can be much higher than what a typical value looks like, misleading stakeholders.

✓ Instead: Use `percentile_cont(0.5) WITHIN GROUP (ORDER BY col)` for the true median, and report p95/p99 for tail latency rather than average.

`percentile_disc` always returns an actual row value and never interpolates. For continuous numeric data (e.g., response times), this can give a noticeably different result from the true mathematical percentile.

✓ Instead: Use `percentile_cont` for continuous numeric or interval data where interpolation between values is statistically correct.

When two or more values share the highest frequency, `mode()` breaks ties by returning the smallest value (per the ORDER BY direction), not by any secondary frequency criterion. This can be surprising.

✓ Instead: If tie-breaking matters for your use case, use `GROUP BY value ORDER BY count(*) DESC, value ASC LIMIT 1` in a subquery, which makes the tie-breaking rule explicit.

`corr()` returns a value between -1 and 1 but gives no indication of whether the sample is large enough for the correlation to be statistically significant. A corr of 0.95 from 3 data points is essentially noise.

✓ Instead: Always pair `corr()` with a `HAVING count(*) > N` guard (typically N ≥ 30) and consider computing a p-value or confidence interval via external tooling for critical decisions.

`regr_slope` fits a straight line. If the relationship between Y and X is exponential, logarithmic, or cyclical, the slope will be misleading and forecasts will be inaccurate.

✓ Instead: Always plot or inspect the data first. For exponential growth, apply `ln()` to the Y values before regression. Use `regr_r2` to validate how well the linear model actually fits.

A common pattern before PG 14 was to fetch all ranges into the application layer and merge overlaps using custom loop logic. This is slow for large datasets and moves work out of the database.

✓ Instead: Use `range_agg` (PG 14+) to compute the union of all ranges server-side in a single aggregate pass, then subtract from the full interval to find gaps.