[SQL] Floating point type to store numbers

[Radhika Sambamurti]

Hi,
I am currently using Postgresql to maintain an application which is used
for trading and back office operations. Currently our monetary fields are
stored in Varchar. I am finding a huge CPU utilization while converting
from varchar to float. I think for reasons unknown to me, we originally
stored $ amounts and rates in varchar. I am planning to convert our tables
that hold money fields and rates from varchar to float. I do not want to
convert to numeric because numeric is a special string type.

The question is: how accurate is floating point numbers in Postgres. We
are using 7.4 soon to be moving to 8.2.
I need the accuracy to about 6 decimal points. I have read that floating
points can convert to numbers in accurately.

Thank you.
Radhika


-- 
It is all a matter of perspective. You choose your view by choosing where
to stand.
Larry Wall
---


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Floating point type to store numbers

[Andrew Sullivan]

On Tue, Apr 17, 2007 at 02:53:54PM -0400, Radhika Sambamurti wrote:
> that hold money fields and rates from varchar to float. I do not want to
> convert to numeric because numeric is a special string type.

I think you should reconsider.  The _only_ correct storage for your
money data (i.e. if you want to do calculations on them) is numeric. 
Float is always wrong, in every application, for this sort of work. 
Obviously, you can store the values as text, but if you want to do
calculations, you'll need to cast (in which case you're casting to
numeric, I hope, or you could lose precision).

> The question is: how accurate is floating point numbers in Postgres. We

As accurate as they are in the underlying C implementation, which is
to say "not accurate enough for financial data".

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Floating point type to store numbers

[Milen A. Radev]

Radhika Sambamurti написа:
> Hi,
> I am currently using Postgresql to maintain an application which is used
> for trading and back office operations. Currently our monetary fields are
> stored in Varchar. I am finding a huge CPU utilization while converting
> from varchar to float. I think for reasons unknown to me, we originally
> stored $ amounts and rates in varchar. I am planning to convert our tables
> that hold money fields and rates from varchar to float. I do not want to
> convert to numeric because numeric is a special string type.
> 
> The question is: how accurate is floating point numbers in Postgres. We
> are using 7.4 soon to be moving to 8.2.
> I need the accuracy to about 6 decimal points. I have read that floating
> points can convert to numbers in accurately.


I believe the manual is quite clear on that one (
http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-FLOAT)
:

" - If you require exact storage and calculations (such as for monetary
amounts), use the numeric type instead."


So if you decide to use floats after this warning you are on your own.


-- 
Milen A. Radev


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Floating point type to store numbers

[Richard Broersma Jr]

---
> The question is: how accurate is floating point numbers in Postgres. We
> are using 7.4 soon to be moving to 8.2.
> I need the accuracy to about 6 decimal points. I have read that floating
> points can convert to numbers in accurately.

http://www.postgresql.org/docs/8.2/interactive/datatype-numeric.html#DATATYPE-FLOAT

I didn't see anything about numeric being a string type,

http://www.postgresql.org/docs/8.2/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

Regards,
Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Floating point type to store numbers

[Radhika Sambamurti]

>
> ---
>> The question is: how accurate is floating point numbers in Postgres. We
>> are using 7.4 soon to be moving to 8.2.
>> I need the accuracy to about 6 decimal points. I have read that floating
>> points can convert to numbers in accurately.
>
> http://www.postgresql.org/docs/8.2/interactive/datatype-numeric.html#DATATYPE-FLOAT
>
> I didn't see anything about numeric being a string type,
>
>

Numeric values are physically stored without any extra leading or trailing
zeroes. Thus, the declared precision and scale of a column are maximums,
not fixed allocations. (In this sense the numeric type is more akin to
varchar(n) than to char(n).) The actual storage requirement is two bytes
for each group of four decimal digits, plus eight bytes overhead.

However, arithmetic on numeric values is very slow compared to the integer
types, or to the floating-point types described in the next section.

http://www.postgresql.org/docs/8.2/static/datatype-numeric.html

http://www.postgresql.org/docs/8.2/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
> Regards,
> Richard Broersma Jr.
>


-- 
It is all a matter of perspective. You choose your view by choosing where
to stand.
Larry Wall
---

-- 
It is all a matter of perspective. You choose your view by choosing where
to stand.
Larry Wall
---


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Floating point type to store numbers

[Radhika Sambamurti]

Andrew,
This has been quite helpful. My main concern is CPU cost.  Thanks for the
input.

--Radhika

> On Tue, Apr 17, 2007 at 02:53:54PM -0400, Radhika Sambamurti wrote:
>> that hold money fields and rates from varchar to float. I do not want to
>> convert to numeric because numeric is a special string type.
>
> I think you should reconsider.  The _only_ correct storage for your
> money data (i.e. if you want to do calculations on them) is numeric.
> Float is always wrong, in every application, for this sort of work.
> Obviously, you can store the values as text, but if you want to do
> calculations, you'll need to cast (in which case you're casting to
> numeric, I hope, or you could lose precision).
>
>> The question is: how accurate is floating point numbers in Postgres. We
>
> As accurate as they are in the underlying C implementation, which is
> to say "not accurate enough for financial data".
>
> A
>
> --
> Andrew Sullivan  | [EMAIL PROTECTED]
> A certain description of men are for getting out of debt, yet are
> against all taxes for raising money to pay it off.
>   --Alexander Hamilton
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>


-- 
It is all a matter of perspective. You choose your view by choosing where
to stand.
Larry Wall
---


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Floating point type to store numbers

[Radhika Sambamurti]

> I believe the manual is quite clear on that one (
> http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-FLOAT)
> :
>
> " - If you require exact storage and calculations (such as for monetary
> amounts), use the numeric type instead."
>
>
> So if you decide to use floats after this warning you are on your own.
>
>
> --
> Milen A. Radev


Well, actually the manual is not quite clear on this one.
It says quite clearly that very very small values approaching zero and
very very large values will be problematic. But I am not calculating the
distance to the moon. I need values to be accurate upto the 6th decimal
place, and was wondering if people use floating point types for this sort
of thing.

Thanks,
Radhika


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] Floating point type to store numbers

[Andrew Sullivan]

On Tue, Apr 17, 2007 at 04:33:33PM -0400, Radhika Sambamurti wrote:
> Andrew,
> This has been quite helpful. My main concern is CPU cost.  Thanks for the
> input.

You're welcome.  Are you sure your main concern should be CPU cost?
It's true that numeric is more costly that float in a lot of cases,
but I know at least one auditor who will refuse to certify results
from programs that anywhere use floating-point storage or calculation
on accounting data.  The problem is really that you can get compound
errors -- very small rounding errors several times can turn out to be
a big problem.  (One quick primer that can help you understand this
is at .)

A
-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The whole tendency of modern prose is away from concreteness.
--George Orwell

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Floating point type to store numbers

[Milen A. Radev]

Radhika Sambamurti написа:
>> I believe the manual is quite clear on that one (
>> http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-FLOAT)
>> :
>>
>> " - If you require exact storage and calculations (such as for monetary
>> amounts), use the numeric type instead."
>>
>>
>> So if you decide to use floats after this warning you are on your own.
> 
> Well, actually the manual is not quite clear on this one.


Given your requirements - "an application which is used for trading and
back office operations." and "our monetary fields...", I still believe
the manual is crystal clear.

> It says quite clearly that very very small values approaching zero and
> very very large values will be problematic. But I am not calculating the
> distance to the moon. I need values to be accurate upto the 6th decimal
> place, and was wondering if people use floating point types for this sort
> of thing.


-- 
Milen A. Radev


---(end of broadcast)---
TIP 6: explain analyze is your friend