PMOD

Return the non-negative remainder of dividing one number by another.

Category: mathReturns: NUMERICDialect: Standard

Syntax

PMOD(dividend, divisor)

Description

## Overview Returns the non-negative remainder of dividing the dividend by the divisor. Unlike MOD, which follows the sign of the dividend, PMOD always returns a value in the range [0, |divisor|). This makes PMOD the right choice whenever the output is used as an index into an array, a shard number, or a hash bucket: a negative bucket index is almost always a bug. PMOD is mathematically equivalent to ((x MOD d) + d) MOD d and matches the Python %-operator convention on integers. ## Behavior - Accepts any numeric type for both arguments. - Returns a non-negative value of the promoted common type. - Returns NULL if either argument is NULL. - Returns NULL when the divisor is zero. - Output is always in the range [0, |divisor|), regardless of the signs of the inputs. ## Numeric precision - For integer types, PMOD is exact. - For DOUBLE and DECIMAL, the same floating-point caveats that apply to MOD also apply here. - The extra adjustment to make the result non-negative does not introduce rounding for integer inputs. ## Compatibility - PMOD is the standard non-negative-modulo primitive in SQL dialects. Matches Python's % operator on integers. - Equivalent to ((MOD(x, d) + d) MOD d) when expressed via MOD.

Parameters

Name	Type	Description
`dividend`		Specifies the value to be divided. Accepts any numeric type.
`divisor`		Specifies the value to divide by. Must be non-zero; a zero divisor returns NULL.

Examples

-- Positive modulo: same as MOD for positive inputs
SELECT PMOD(10, 3);
-- Result: 1

-- Negative dividend: result is still non-negative
SELECT PMOD(-10, 3);
-- Result: 2

-- Compare MOD vs PMOD for negative dividend
SELECT MOD(-10, 3), PMOD(-10, 3);
-- Result: -1, 2

-- Division by zero
SELECT PMOD(10, 0);
-- Result: NULL

-- Evenly divisible
SELECT PMOD(12, 4);
-- Result: 0

-- NULL propagation
SELECT PMOD(NULL, 3);
-- Result: NULL

Pitfalls

PMOD and MOD differ for negative dividends. PMOD(-10, 3) = 2 and MOD(-10, 3) = -1. Do not substitute one for the other silently.
Division by zero returns NULL. If your divisor can be zero, use NULLIF or CASE to handle the no-op branch explicitly.
PMOD of a DOUBLE is subject to the same floating-point caveats as MOD. Prefer integer or DECIMAL inputs for exact bucketing.
The divisor's sign does not affect the result: PMOD(x, -3) and PMOD(x, 3) return the same non-negative value. Pass ABS(divisor) if you want that to be explicit in your SQL.
PMOD of a non-deterministic hash function is only as stable as the hash. Use stable hash functions (for example, MD5-based) when PMOD output is used as a durable shard identifier.