ZEROIFNULL

Return 0 if the input is NULL, otherwise return the input unchanged.

Category: miscReturns: NUMERICDialect: PostgreSql

Syntax

ZEROIFNULL(expr)

Description

## Overview Returns 0 when the input is NULL and returns the input unchanged otherwise. Use this function to normalize optional numeric columns before aggregation, to satisfy a downstream NOT NULL constraint, or to make arithmetic expressions treat missing values as zero. ZEROIFNULL is the inverse of NULLIFZERO. ## Behavior - Returns 0 if the input is NULL. - Returns the input unchanged otherwise. - The return type is the same as the input numeric type; 0 is cast to that type. - Deterministic and side effect free. ## Compatibility - Semantically equivalent to `COALESCE(expr, 0)`, but expressed as a single-argument helper that avoids the literal and matches terminology used in NULLIFZERO.

Parameters

Name	Type	Description
`expr`		Specifies the numeric expression to evaluate. Returns 0 when the value is NULL; otherwise returns the value unchanged.

Examples

-- NULL becomes 0
SELECT ZEROIFNULL(NULL);  -- 0

-- Non-NULL passes through unchanged
SELECT ZEROIFNULL(42);  -- 42

-- Safe aggregation when some rows have NULL discount
SELECT SUM(ZEROIFNULL(discount)) AS total_discount
FROM sales.catalog.orders;

-- Contrast with NULLIFZERO
SELECT ZEROIFNULL(NULL) AS z, NULLIFZERO(0) AS n;  -- 0 and NULL

-- Guarantee a non-NULL projection for a downstream NOT NULL column
SELECT ZEROIFNULL(net_amount) AS net_amount FROM sales.catalog.orders;

-- NULL in a divisor becomes 0 (which may still produce a divide-by-zero)
SELECT amount / ZEROIFNULL(rate) FROM sales.catalog.orders;

Pitfalls

ZEROIFNULL changes NULL to zero, which affects aggregates: AVG over a column with many NULLs drops to a smaller value after ZEROIFNULL because the denominator of AVG now counts those rows.
Treating missing measurements as zero can skew analytics. Consider whether NULL means 'unknown' (and should stay NULL) or 'absent' (and can reasonably become 0).
Using ZEROIFNULL in a divisor does not prevent divide-by-zero errors; it replaces NULL with 0, which is itself an illegal divisor. Wrap the divisor in NULLIFZERO instead.
ZEROIFNULL returns a value of the input type. If the input column is NUMERIC(38,2), the output literal 0 is coerced to NUMERIC(38,2); do not mix types inside COALESCE chains.