Re: rounding problems

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