NUM_NONNULLS

Return the count of non-NULL arguments in a variadic argument list.

Category: miscReturns: INTDialect: PostgreSql

Syntax

NUM_NONNULLS(expr1, expr2, ...)

Description

## Overview Returns the number of arguments that are not NULL. Accepts any number of expressions of any type. Use this function to measure data completeness, to gate logic on the number of populated optional fields, or to build per-row fill-rate metrics. ## Behavior - Requires at least one argument. - Returns an INT count in the range 0 to the number of arguments. - Never returns NULL, even when every argument is NULL (returns 0). - Arguments can be of different types. - Each argument is evaluated exactly once. - Deterministic and side effect free. ## Compatibility - Matches the PG-compat NUM_NONNULLS function with variadic arguments.

Parameters

Name	Type	Description
`expr`		Specifies one or more expressions to examine. Each argument is evaluated exactly once; non-NULL values are counted, NULL values are ignored.

Examples

-- Count non-NULL values in a mixed list
SELECT NUM_NONNULLS(1, NULL, 3, NULL, 5);  -- 3

-- All arguments non-NULL
SELECT NUM_NONNULLS('a', 'b', 'c');  -- 3

-- All arguments NULL
SELECT NUM_NONNULLS(NULL, NULL);  -- 0

-- Measure how many optional survey fields are populated
SELECT response_id, NUM_NONNULLS(q1, q2, q3, q4, q5) AS answered
FROM survey.catalog.responses;

-- Require at least three populated fields
SELECT customer_id FROM crm.catalog.customers
WHERE NUM_NONNULLS(email, phone, mobile, fax) >= 3;

Pitfalls

Only SQL NULL counts as missing. Empty strings and zero values are counted as populated; wrap the argument in NULLIFEMPTY or NULLIFZERO if those should be treated as absent.
There is no short-circuit evaluation; all arguments are evaluated even if the first few are already non-NULL. Expensive expressions inside NUM_NONNULLS can dominate query cost.
The function cannot accept zero arguments; a call with an empty list is a syntax error.
NUM_NONNULLS does not expand `*`. Pass each column explicitly.