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,

I think the default scale is 6. Try using something like `decimal (18, 8)`. — Gordon Linoff, Jul 10 '15 at 21:21
@GordonLinoff, don't work too. see: http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/1534 — user1221679, Jul 10 '15 at 22:03
What client application are you using? Exact PostgreSQL version (from `SELECT version()`? — Craig Ringer, Jul 11 '15 at 07:16

score 6 · Accepted Answer · edited Jan 05 '22 at 08:38

6

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.

edited Jan 05 '22 at 08:38

Nimantha

6,405
6
28
69

answered Jul 11 '15 at 11:09

Gordon Linoff

1,242,037
58
646
786

you are right, casting as text returns value not in exponential/scientific notation. For just displaying value (instead of calculations) this is what i need. Here goes updated sqlfiddle demo: http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/1550 – user1221679 Jul 11 '15 at 14:01

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

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

enter image description here

edited May 23 '17 at 11:58

Community

1
1

answered Jul 10 '15 at 21:19

Juan Carlos Oropeza

47,252
12
78
118

postgresql v9.4.. see http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/1534 – user1221679 Jul 10 '15 at 22:04

postgresql round division

2 Answers2