Shape / columns · All operations

Do arithmetic that tolerates nulls and zeros

A variant of Apply a maths function

Add or subtract with nulls treated as zero, or divide with null returned when the denominator is zero.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Add and subtract with nulls as zero, and divide with null for a zero denominator

SQL
CREATE TEMP TABLE pairs AS SELECT * FROM (VALUES
  (1, 10, 4), (2, NULL, 4), (3, 10, NULL), (4, 10, 0), (5, 0, 0), (6, NULL, NULL)
) t(id, a, b);
-- a plain 10 // 0 stops with an error on 2.0, and 10 / 0 on doubles gives infinity; nullif turns the zero into a missing value
CREATE TEMP TABLE result AS
SELECT id, coalesce(a, 0) + coalesce(b, 0) AS safe_sum, coalesce(a, 0) - coalesce(b, 0) AS safe_difference,
       a // nullif(b, 0) AS safe_whole, a / nullif(b, 0) AS safe_ratio FROM pairs;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(safe_sum ORDER BY id) FROM result) = [14, 4, 10, 10, 0, 0] AND (SELECT list(safe_difference ORDER BY id) FROM result) = [6, -4, 10, 10, 0, 0] AND (SELECT list(safe_whole ORDER BY id) FROM result) = [2, NULL, NULL, NULL, NULL, NULL] AND (SELECT list(safe_ratio ORDER BY id) FROM result) = [2.5, NULL, NULL, NULL, NULL, NULL]) THEN 'as expected' ELSE error('nulls were not treated as zero in the sum and difference, or a zero or missing denominator did not give a null ratio') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “nulls were not treated as zero in the sum and difference, or a zero or missing denominator did not give a null ratio”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Bruin (3)
dbt-utils (3)
  • safe_addread 2026-09-27

    Sum nullable fields, treating nulls as zero

  • safe_divideread 2026-09-27

    Divide two expressions, returning null when the denominator is 0

  • safe_subtractread 2026-09-27

    Subtract nullable fields, treating nulls as zero

SQLMesh (3)
  • @SAFE_ADDread 2026-09-27

    Add two or more operands treating NULL as 0; NULL when all are NULL

  • @SAFE_DIVread 2026-09-27

    Divide two numbers, returning NULL when the denominator is 0

  • @SAFE_SUBread 2026-09-27

    Subtract two or more operands treating NULL as 0; NULL when all are NULL

Background

Null-safe arithmetic adds or subtracts with missing values treated as zero, and divides with NULL returned when the denominator is zero.[1][2] In SQL, a mathematical operator applied to NULL gives an unknown result, represented by NULL, so 10 * NULL is NULL.[3]

When to use it

In DuckDB, integer division (//), modulo (%) and INTERVAL / 0 throw an Invalid Input Error on division by zero instead of returning NULL.[4]

Dividing NULL by zero returns NULL rather than a division-by-zero error on platforms including Oracle, PostgreSQL, MySQL Server and Microsoft SQL Server, though the ISO SQL standard does not define this behaviour.[3] Treat a missing amount as zero only where missing really means none, because a total built from zeros in place of unknowns looks like a known total. (Meridian's view) Decide downstream what a NULL ratio from a zero denominator means, since it records that the ratio could not be computed, not that it is zero. (Meridian's view)

The method

The dbt_utils safe_add and safe_subtract macros wrap each field in coalesce(field, 0) and join the results with + or -.[5][6] The dbt_utils safe_divide macro divides the numerator by nullif(denominator, 0).[7] In DuckDB, nullif(a, b) returns NULL if a = b and otherwise returns a, the same as CASE WHEN a = b THEN NULL ELSE a END.[8] In DuckDB, coalesce returns for each row the first argument that is not NULL, and returns NULL only if all its arguments are NULL.[9] SQL defines NULLIF and COALESCE as abbreviations for searched CASE expressions.[3]

E. F. Codd's 1979 paper in the ACM Transactions on Database Systems is the one most commonly cited for the semantics of Null as adopted in SQL, and its Section 2.3 details Null propagation in arithmetic operations.[3]

Compared with the alternatives

DuckDB's TRY expression turns errors caused by input rows in its child expression into NULL for those rows, so TRY(1 // 0) is NULL and so is TRY('abc'::INTEGER).[10] Use an aggregate such as sum to total a column, and null-safe addition to total several columns within one row. (Meridian's view)

Sources

  1. [1]README.mddocumentation
  2. [2]Macros | Bruin CLIdocumentation
  3. [3]Null (SQL) - Wikipediasecondary source
  4. [4]Numeric Functions – DuckDBdocumentation
  5. [5]safe_add.sqldocumentation
  6. [6]safe_subtract.sqldocumentation
  7. [7]safe_divide.sqldocumentation
  8. [8]Utility Functions – DuckDBdocumentation
  9. [9]NULL Values – DuckDBdocumentation
  10. [10]TRY Expression – DuckDBdocumentation