PostgreSQL – How to Truncate Double Precision Number Without Rounding

postgresql

I have '9.999999999'::double precision number and I want to convert it to decimal(8,2) without any rounding. The result I am expecting is 9.99. So far I tried:

select trunc('9.999'::double precision::numeric(8,3), 2)

and it works but I have to know number of precision digits for double precision value because if there will be more 9 then ::numeric(8,3) will automatically round it to 10.000.

What I can do about it?

Best Answer

Why not simply do the following?

SELECT trunc(num, 2) 
 FROM (VALUES (         9.999999                  ), 
              (         9.999999999999999999999999), 
              (9999999999.999999999999999999999999)
      ) t(num);

/*
     trunc     
───────────────
          9.99
          9.99
 9999999999.99
*/

This way you just truncate it, without assuming anything about the digits.

Notes:

  • it looks like you have numbers stored as text. Avoid this, if you can. If this is not the case, just don't explicitly cast anything. If it is, then:
  • It might not be obvious, but my example is using numeric - this is the type the parser interprets 9.99999, for example:
SELECT pg_typeof(9.99999);
 pg_typeof 
───────────
 numeric

That is, use an unlimited numeric in your example. This would accomodate all your digits.

  • According to the documentation:

    The types decimal and numeric are equivalent.

Related Question