NULLIFZERO

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

Category: miscReturns: NUMERICDialect: PostgreSql

Syntax

NULLIFZERO(expr)

Description

## Overview Returns NULL when the input is exactly zero, and returns the input unchanged otherwise. The canonical use is to protect division expressions from a divide-by-zero error: `x / NULLIFZERO(y)` evaluates to NULL when y is zero rather than raising an error. NULLIFZERO is the inverse of ZEROIFNULL. ## Behavior - Returns NULL if the input is NULL or is exactly 0. - Returns the input unchanged otherwise. - Works for both integer and decimal types; the comparison is value-based, not type-based. - Negative zero in floating-point (`-0.0`) compares equal to `0.0` and therefore returns NULL. - Deterministic and side effect free. ## Compatibility - Semantically equivalent to `NULLIF(expr, 0)`, but expressed as a single-argument helper that accepts any numeric type without an explicit literal zero.

Parameters

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

Examples

-- Zero becomes NULL
SELECT NULLIFZERO(0);  -- NULL

-- Non-zero passes through
SELECT NULLIFZERO(42);  -- 42

-- Prevent divide-by-zero in a ratio
SELECT revenue / NULLIFZERO(cost) AS margin_ratio
FROM fin.catalog.financials;

-- Handle both integer and decimal zero
SELECT NULLIFZERO(0.0) AS dec_result, NULLIFZERO(0) AS int_result;  -- NULL, NULL

-- NULL input stays NULL
SELECT NULLIFZERO(NULL);  -- NULL

-- Combine with COALESCE for a default
SELECT COALESCE(NULLIFZERO(cost), 1) AS safe_cost FROM fin.catalog.financials;

Pitfalls

NULLIFZERO converts zero to NULL, which then propagates through arithmetic. Downstream aggregates that ignore NULL (AVG, SUM) will skip these rows, which is usually desired but can hide issues if you actually wanted to count them.
For floating-point inputs, values that are mathematically close to zero (for example, 1e-300) are not treated as zero. Round or compare against an epsilon upstream if near-zero should also become NULL.
NULLIFZERO is a single-argument helper and is not the same as NULLIF: `NULLIF(a, b)` takes two arguments and returns NULL only when `a = b`.
On a column declared NOT NULL, wrapping the expression in NULLIFZERO may silently introduce NULLs that the downstream cast or constraint does not tolerate. Pair with COALESCE if you need a non-NULL default.