Thread: Floating point type to store numbers
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 ---
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 | ajs@crankycanuck.ca 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
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
--- > 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.
> > --- >> 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 ---
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 | ajs@crankycanuck.ca > 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 ---
> 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
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 <http://www2.hursley.ibm.com/decimal/decifaq1.html>.) A -- Andrew Sullivan | ajs@crankycanuck.ca The whole tendency of modern prose is away from concreteness. --George Orwell
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
Andrew Sullivan wrote: > 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 <http://www2.hursley.ibm.com/decimal/decifaq1.html>.) Would you rather have wrong answers really, really fast, or right answers fast enough? -- Lew