Postgresql - truncate numbers to range

Question

Is there a built-in function to truncate values of one type to fit the range of another type? E.g casting a INTEGER to SMALLINT. This might fail if the INTEGER is larger than SMALLINT can hold. I would like the number to be truncated to the largest possible SMALLINT in this case.

Answer 1

No inbuilt function I'm aware of, you would need to use a case expression to handle the min and max values e.g:

SELECT CASE
    WHEN your_integer_column > 32767 THEN 32767
    WHEN your_integer_column < -32768 THEN -32768
    ELSE your_integer_column
END::smallint AS truncated_smallint
FROM your_table;