How to clamp a float in PostgreSQL

Question

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) );

Ulysse BN · Accepted Answer · 2018-10-25T12:41:32.423

6

The solution I found was:

SELECT ACOS(GREATEST(-1, LEAST(1, 1 + 0.0000000001));

edited Oct 25 '18 at 12:41

answered Oct 25 '18 at 09:23

Ulysse BN

10,116
7
54
82

I would love to see a CLAMP function though. And I am not sure about perf issues, this is on a critical request – Ulysse BN Oct 25 '18 at 09:24
1

That's as fast as it gets unless you want to write a C function (which is a viable option if there is a premium on performance). – Laurenz Albe Oct 25 '18 at 09:44
@LaurenzAlbe in fact, could you maybe add an answer with that C function you are talking about? I think that would be pretty useful. – Ulysse BN Oct 17 '19 at 09:09

score 3 · Answer 2 · answered Oct 16 '19 at 16:36

-- 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;

Quite an interesting answer! However IMHO both CLAMP_BELOW and CLAMP_ABOVE are not clearer than LEAST and GREATEST. And I would love to have that function but not to have to add it to every single PG database I want to work on. +1 for the idea though — Ulysse BN, Oct 16 '19 at 22:25

How to clamp a float in PostgreSQL

2 Answers2