postgresql round division

Question

I want to divide values and round them up to 8 decimal places but i found that some divisions return in scientific notation.

How can i always get round division without scientific notation?

• select round( 123/100000000::decimal, 8 )

  returns 0.00000123 as expected.

• select round( 1/100000000::decimal, 8 )

  returns 1e-8 but... i was expecting 0.00000001

How can i round 1/100000000 to 8 decimal places and return 0.00000001 ?

sql fiddle: http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/1534

Thanks for help. best,

Answer 1

Actually, when I try your code in Postgres 9.3.4 using pgAdmin, both return the values you want. The values are not in exponential notation. Hence, I suspect this is an issue with your application, not the database.

An easy way to check is to put the value as a string:

select round( 1/100000000::decimal, 8 )::text

This should not return exponential notation.

Answer 2

Probably second case go beyond precision range 8.

You can check this Question Selecting floating point numbers in decimal form

Also what version are you working with because in pgAdmin 9.2 I get a different result