Re: rounding problems

Justin <justin@xxxxxxxxxxxxxxx> · Mon, 12 May 2008 17:23:15 -0400

I tried casting them to numeric and it was still wrong

OK i just added decimal point after the 9 and 1  it work at that point.

Thats an odd result i would not have expected it to do that.

This prompts another question how does postgres figure out the data
types passed in an SQL string???

Andy Anderson wrote:
I would guess the issue is that 9/10 is an integer calculation, with 
result 0. Use instead 9./10 or 9/10. or 9./10. with result 0.9.

-- Andy

On May 12, 2008, at 5:09 PM, Justin wrote:

As i'm playing around with rounding and the numeric field precision 
ran into a odd set of results i don't understand

here is the sql i wrote the first four inserts are calculations we 
run everyday and they make sense but if  division is used the results 
are not right or am i missing something

create table test_num (
   num1 numeric(20,1),
   num2 numeric(20,2),
   num3 numeric(20,3),
   num4 numeric(20,4),
   num5 numeric(20,5),
   num6 numeric(20,6),
   num7 numeric(20,7),
   num8 numeric(20,8),
   num9 numeric(20,9));

delete from test_num;

insert into test_num values ( (0.70 *1.05), (0.70 *1.05), (0.70 *1.05),
                 (0.70 *1.05), (0.70 *1.05), (0.70 *1.05),
                 (0.70 *1.05), (0.70 *1.05), (0.70 *1.05));

insert into test_num values ( (0.709 *1.05), (0.709 *1.05), (0.709 
*1.05),
                 (0.709 *1.05), (0.709 *1.05), (0.709 *1.05),
                 (0.709 *1.05), (0.709 *1.05), (0.709 *1.05));
                insert into test_num values( (.5/.03), (.5/.3), (.5/3),
                (.5/30), (.5/300), (.5/3000),
                (.5/30000), (.5/30000), (.5/30000));

insert into test_num values( (.5/.03)*.9975, (.5/.3)*.9975, 
(.5/3)*.9975,
                (.5/30)*.9975, (.5/300)*.9975, (.5/3000)*.9975,
                (.5/30000)*.9975, (.5/30000)*.9975, (.5/30000)*.9975);
                insert into test_num values( (9*.1),
                (9*.01),
                (9*.001),
                (9*.0001),
                (9*.00001),
                (9*.000001),
                (9*.0000001),
                (9*.00000001),
                (9*.000000001));

insert into test_num values ( (9/10),
                 (9/100),
                 (9/1000),
                 (9/10000),
                 (9/100000),
                 (9/1000000),
                 (9/10000000),
                 (9/100000000),
                 (9/1000000000));
                insert into test_num values( (1*.1),
                (1*.01),
                (1*.001),
                (1*.0001),
                (1*.00001),
                (1*.000001),
                (1*.0000001),
                (1*.00000001),
                (1*.000000001));
               insert into test_num values ( (1/10),
                 (1/100),
                 (1/1000),
                 (1/10000),
                 (1/100000),
                 (1/1000000),
                 (1/10000000),
                 (1/100000000),
                 (1/1000000000));

select * from test_num ;

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general