How to clamp a float in PostgreSQL

user_6320039 0
Viewed 1478

I have a number 1.00000001, and I want to clamp it between -1 and 1 to avoid input out of range error on ACOS() function. An MCVE look like this:

SELECT ACOS( 1 + 0.0000000001 );

My ideal would be something like:

SELECT ACOS( CLAMP(1 + 0.0000000001, -1, 1) );
postgresql-10 postgresql sql
2 Answers

The solution I found was:

SELECT ACOS(GREATEST(-1, LEAST(1, 1 + 0.0000000001));
user_63200390 
-- example: clamp(subject, min, max)
CREATE FUNCTION clamp(integer, integer, integer) RETURNS integer
    AS 'select GREATEST($2, LEAST($3, $1));'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

-- example: clamp_above(subject, max)
CREATE FUNCTION clamp_above(integer, integer) RETURNS integer
    AS 'select LEAST($1, $2);'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

-- example: clamp_below(subject, min)
CREATE FUNCTION clamp_below(integer, integer) RETURNS integer
    AS 'select GREATEST($1, $2);'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
user_35743790
Related