2

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?

4

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.