Re: rounding problems - Mailing list pgsql-general

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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>