Provides mathematical clamp functions for numeric types in DuckDB, including general clamping, saturate, and others. Ensures values stay within a specified range and handles NULLs and NaNs correctly.
Maintainer(s):
oglego
Installing and Loading
INSTALL clamp FROM community;
LOAD clamp;
Example
-- General clamp between min and max
SELECT clamp(5, 0, 10); -- Returns 5
SELECT clamp(-5, 0, 10); -- Returns 0
SELECT clamp(15, 0, 10); -- Returns 10
-- NULL handling
SELECT clamp(NULL, 0, 10); -- Returns NULL
SELECT clamp(5, NULL, 10); -- Returns NULL
SELECT clamp(5, 0, NULL); -- Returns NULL
-- Convenience clip (alias for clamp)
SELECT clip(15, 10, 20); -- Returns 15
SELECT clip(5, 10, 20); -- Returns 10
SELECT clip(25, 10, 20); -- Returns 20
-- Saturate (equivalent to clamp(x, 0.0, 1.0))
SELECT saturate(0.5); -- Returns 0.5
SELECT saturate(-0.5); -- Returns 0.0
SELECT saturate(1.5); -- Returns 1.0
-- Convenience clamp01 (alias for saturate)
SELECT clamp01(0.7); -- Returns 0.7
SELECT clamp01(-1.0); -- Returns 0.0
SELECT clamp01(2.0); -- Returns 1.0
-- Handling NaN and NULL
SELECT clamp(CAST('NaN' AS DOUBLE), 0, 10); -- Returns NaN
SELECT saturate(NULL); -- Returns NULL
-- Wrap a value into a range [min, max) using modular arithmetic
SELECT wrap(11, 0, 10); -- Returns 1
SELECT wrap(25, 10, 20); -- Returns 15
SELECT wrap(5, 10, 20); -- Returns 15
-- Pingpong a value between min and max
SELECT pingpong(11, 0, 10); -- Returns 9
SELECT pingpong(12, 10, 20); -- Returns 12
SELECT pingpong(28, 10, 20); -- Returns 12
-- Fract (returns the fractional part of a number)
SELECT fract(1.75); -- Returns 0.75
SELECT fract(-0.1); -- Returns 0.9
SELECT fract(10); -- Returns 0.0
About clamp
The Clamp extension provides functions for numerical restriction:
- clamp(value, min, max): Restricts a numeric value between
minandmax.- Supports NaN propagation (IEEE-754 standard)
- Throws an exception if
min > max - Returns NULL if any argument is NULL
-
clip(value, min, max): Alias for
clamp(value, min, max). -
saturate(value): Convenience function to clamp values between 0.0 and 1.0.
-
clamp01(value): Alias for
saturate(value). -
wrap(value, min, max): Wrap a value x into the range [min, max) using modular arithmetic.
-
pingpong(value, min, max): Return a value that "bounces" back and forth between the min and max.
- fract(value): Return the fractional part of a number.
This extension ensures safe handling of edge cases including:
- NaN values
- NULLs
- Floating-point numbers
- Large integers
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| clamp | scalar | NULL | NULL | |
| clamp01 | scalar | NULL | NULL | |
| clip | scalar | NULL | NULL | |
| fract | scalar | NULL | NULL | |
| pingpong | scalar | NULL | NULL | |
| saturate | scalar | NULL | NULL | |
| wrap | scalar | NULL | NULL |