COALESCE returns the first non-NULL (order matters); NULLIF nulls out a sentinel value.

COALESCE is ordered fallback — the order is the policy (measured-value, then estimate, then 0), and its classic misread is thinking it skips 'empty-ish' values: 0 and '' are values and they win. NULLIF is the inverse — it creates NULL on a sentinel match, exactly what safe division wants (denominator 0 → NULL → the whole ratio NULL). The pair composes: COALESCE(x / NULLIF(y, 0), 0).

SELECT
  spot,
  COALESCE(measured, 0, estimate) AS val
FROM
  readings
ORDER BY
  spot;

SELECT
  spot,
  COALESCE(measured, estimate, 0) AS val
FROM
  readings
ORDER BY
  spot;

The reading fell back to 0 instead of the estimate — COALESCE returns the first non-NULL and 0 is a value, so putting 0 before the estimate means the estimate is never reached.

The composed guard COALESCE(x / NULLIF(y,0), 0); sentinel-to-NULL before aggregates.