Downloads 9.2kthis week

GitHub stars 2

Extension descriptor (YAML)

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.

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

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

-- Step function (returns 0 if value < edge, otherwise 1)
SELECT step(0.5, 0.25);      -- Returns 0.0
SELECT step(0.5, 0.5);       -- Returns 1.0
SELECT step(5,6);            -- Returns 1

About clamp

The Clamp extension provides functions for numerical restriction:

1. clamp(value, min, max): Restricts a numeric value between min and max.
   • Supports NaN propagation (IEEE-754 standard)
   • Throws an exception if min > max
   • Returns NULL if any argument is NULL

2. clip(value, min, max): Alias for clamp(value, min, max).

3. saturate(value): Convenience function to clamp values between 0.0 and 1.0.

4. clamp01(value): Alias for saturate(value).

5. wrap(value, min, max): Wrap a value x into the range [min, max) using modular arithmetic.

6. pingpong(value, min, max): Return a value that "bounces" back and forth between the min and max.

7. fract(value): Return the fractional part of a number.

8. step(edge, value): Return 0 if value < edge, otherwise return 1.

This extension ensures safe handling of edge cases including:

• NaN values
• NULLs
• Floating-point numbers
• Large integers

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

This extension does not add any settings.