Обсуждение: postgres arithmetic: raising to nth power
[Using postgres (PostgreSQL) 7.4.7 on
GNU/Linux 2.4.27-1-386 - Debian/Sarge]
----------------------------------------
Hi all,
testing a few arithmetic calculations to make sure I understood how
they worked before including them in a a query I met with the following
results:
select 100*1.10^1277/365::float: # supposedly: first calculates 1.10^1277
---------------------------------- # then multiplies by 100 and divide by 365
1.97772581606028e+52
(1 row)
# Let's see if my guess is correct:
select 100*(1.10)^1277
----------------------
7.21869922862002e+54
(1 row)
select 7.21869922862002/365
-----------------------------
0.01977725816060279452
(1 row)
# Hmmm: same result, except there is no multiplication by 100!
select 7.21869922862002/365::float
------------------------------------
0.0197772581606028
(1 row)
# the presence of either float or numeric dowsn't alter the result.
select 100*(1.10)^1277::float
-------------------------------
7.21869922862002e+54
(1 row)
# However, using more reasonable numbers:
select (1.10)^12
------------------
3.138428376721
(1 row)
select 100*(1.10)^12
----------------------
313.8428376721
(1 row)
the result is correct.
-------------------------------------
So, the problem is: how can I get to know which results are correct and
which aren't? May be the presence on the trailing 'e+..' is signalling
there is an error?
Could somebody give me a short explanation or suggest further readings?
Thanks for your attention.
Regards,
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
Ennio-Sr <nasr.laili@tin.it> writes:
> So, the problem is: how can I get to know which results are correct and
> which aren't? May be the presence on the trailing 'e+..' is signalling
> there is an error?
I take it you've never seen floating-point notation before?
Read the "e" as "times ten to the power of".
regards, tom lane
> Hi all, > testing a few arithmetic calculations to make sure I understood how > they worked before including them in a a query I met with the following > results: I'm not sure I understand what you're trying to say. Except for the fact that you encounter rounding errors (which shouldn't come as a surprise with an exponent of 54) everything seems to work "normally" as far as I'm concerned. If you need arbitrary precision don't do the calculation using a select statement but rather utilise CLN or some other special maths library. Cheers, Andrej
* Andrej Ricnik-Bay <andrej.groups@gmail.com> [300905, 10:34]:
> > Hi all,
>
> I'm not sure I understand what you're trying to say.
>
>
> Except for the fact that you encounter rounding errors (which
> shouldn't come as a surprise with an exponent of 54) everything
> seems to work "normally" as far as I'm concerned.
>
Well, more than a rounding, as it gives a result which misses the
multiplication by 100 and - apparently - is not issuing a warning to
this effect.
> If you need arbitrary precision don't do the calculation using
> a select statement but rather utilise CLN or some other special
> maths library.
>
Never dealt with CLN; just discovered it means Class Library for Numbers
and seems to be an independent math software (Google fires around 61000
items for 'cln' ...) to be used on its own (i.e. I don't think I could
call it from postgres, could I?)
I'm trying to do some financial calculations using data contained in a
pg_db table via select command given from a bash script.
Anyway, thanks for your answer, Andrej.
Regards,
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
* Tom Lane <tgl@sss.pgh.pa.us> [290905, 18:27]:
> Ennio-Sr <nasr.laili@tin.it> writes:
> > So, the problem is: how can I get to know which results are correct and
> > which aren't? May be the presence on the trailing 'e+..' is signalling
> > there is an error?
>
> I take it you've never seen floating-point notation before?
>
> Read the "e" as "times ten to the power of".
>
> regards, tom lane
Touche' .... :-)
Of course, I did see floating-point notation before, but that doesn't
mean I recalled how I should interpret it .... ;)
[I left school many, many, many, many, many years ago and could not find
my old financial maths book yet!]
Back to my problem, rivisited at the light of your (formerly obscure to me ;) )
'clarification':
1 - select 100*(1.10)^1277
----------------------
7.21869922862002e+54
^^
2 - select (1.10)^1277
----------------------
7.21869922862002e+52
^^
I should have noticed the difference in the 'trailing numbers' ... but I
was diverted by other problems .....;(
§§§§§§§§§§§§§§§§
[To better answer Andrej's question:]
# Suppose I invest one dollar at a 10% compound rate for a period of 3
# years; at the end I should get :
select (1+.10)^3
-----------------
1.331
# Now the reverse: what is the IRR on my investment considered that I
# got $ 1.331 after a 3 years period?
select (1.331)^(1/3::float)-1
-------------------------------
0.1
# or, to get a finer result:
select to_char( (1.331)^(1/3::float)-1,'9999.0000' )
------------------------------------------------------
.1000
(1 row)
§§§§§§§§§§§§§§§§§§
Thanks again.
Regards,
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]