Question 233510 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – truncate numbers to range

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    Not built in, but you can convert it

    CREATE TABLE num(i int, s smallint)

    INSERT INTO num VALUES(65355, NULL)

    UPDATE num SET s = ((i::bit(16) ::bit(32)::int4) >> 16)::int2;

    UPDATE 1

    SELECT * fROM num
    i s
    65355 -181

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search