LEARN. PRACTICE. GROW.Conditional aggregates without losing the population
A report can need total requests and status subtotals on the same row. FILTER limits inputs to its aggregate; it does not replace WHERE filtering the entire query. With three settled, two failed, and one pending, metrics are six overall, three settled, and two failed. Also check CASE expressions: COUNT counts both 1 and 0 because both are non-null. To count failures, use an expression producing a value only for failures, sum 1/0 indicators, or apply FILTER. Then compare group totals with the overall total only if categories are genuinely disjoint and cover the whole population.
Denominators, precision, and means
A mean is interpretable only alongside its population. Two requests averaging 10 ms and eight averaging 30 ms produce a combined mean of 26 ms, not 20 ms. Retain sum and count when combining groups; percentiles cannot be combined with the same formula. NULL also changes the denominator: AVG over 20,NULL,40 gives 30, while replacing NULL with zero gives 20. In PostgreSQL, integer division can discard the fraction. Cast an operand before division when the contract needs a decimal result. Also define treatment of a zero denominator instead of letting presentation invent a rate.
Reconciliation: presence before difference
A numerical difference of zero does not by itself establish a valid match. First determine whether the key exists on both sources; then check whether required amounts are known; only then compare values. In the example, ID 1 exists on both sources and differs by 10, ID 2 exists only in ledger with an unknown amount, ID 3 exists only in statement, and ID 4 matches with zero difference. amount_unknown refers to amounts on present rows; a missing row is described separately in coverage. This separation prevents COALESCE from manufacturing zeros and turning two information gaps into apparently successful reconciliation.
Identity, multiplicity, and relationships
Two equal-amount payments can be distinct operations. UNION removes equal projected rows, while UNION ALL retains occurrences. Selecting only amount loses the identity needed to distinguish legitimate payments from retransmissions. EXCEPT also has set-difference semantics and an ALL variant retaining multiplicities in PostgreSQL. In joins, examine every one-to-many relationship: two fees and three exceptions can produce six rows for one account. Aggregate each relationship to the needed grain before joining results. SUM(DISTINCT amount) is not a general repair because two legitimate fees can have equal amounts. Reconcile counts and keys as well as totals.
CTEs as inspectable stages
Use CTEs to name reasoning stages: eligible population, account totals, matches, and final classification. Measure ID counts at each boundary. If twelve remain before filtering and nine afterward, inspect the three removed identities before changing an index. Readability does not guarantee performance. In PostgreSQL 18, a side-effect-free SELECT CTE can be folded into the main query; multiple references and materialization options influence the plan. NOT MATERIALIZED can enable earlier filters and also repeat computation. Compare results and plans in an authorized environment with representative data. Do not turn behavior observed in one specific plan into a universal rule for every query.
Lab: explain every exception
Run the example reconciliation and confirm all four expected keys. For each row, explain coverage, amount_unknown, and difference separately. Add a statement row for ID 2 with amount 0: coverage becomes both, but the ledger amount remains unknown and the difference should not become zero. Then introduce two equal fees for an account and compare the sum before and after joining detail. For daily UTC reports, use boundaries including the start and excluding the end, avoiding counting midnight on two days. Close the investigation with a small table of predicted results, observations, and assumptions that another person can reproduce.
WITH ledger(id, amount) AS (
VALUES (1,100), (2,NULL), (4,70)
), statement(id, amount) AS (
VALUES (1,90), (3,50), (4,70)
)
SELECT COALESCE(l.id,s.id) AS id,
CASE WHEN l.id IS NULL THEN 'missing_ledger'
WHEN s.id IS NULL THEN 'missing_statement'
ELSE 'both' END AS coverage,
CASE WHEN (l.id IS NOT NULL AND l.amount IS NULL)
OR (s.id IS NOT NULL AND s.amount IS NULL)
THEN 1 ELSE 0 END AS amount_unknown,
CASE WHEN l.id IS NOT NULL AND s.id IS NOT NULL
AND l.amount IS NOT NULL AND s.amount IS NOT NULL
THEN l.amount - s.amount ELSE NULL END AS difference
FROM ledger l FULL JOIN statement s ON s.id = l.id
ORDER BY COALESCE(l.id,s.id)The reconciliation separates a difference of 10, an absent source with an unknown amount, another unmatched key, and a match with zero difference.
Common pitfalls
Confusing absence with zero, summing values repeated by joins, using unweighted means of means, or assuming every CTE improves the plan.
Related topics: Join without multiplying mistakes · Count and group carefully · Indexes and execution plans
Establish population, identity, and grain first; only then interpret totals, percentages, and differences.
Reference: PostgreSQL 18: Table Expressions · PostgreSQL 18 reference semantics; DR SQL 2026.2