English

clamp

Downloads

906

GitHub stars

2

Extension repository on GitHub

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:

  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.

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

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.