Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

#1
April 28th, 2009, 09:00 AM
 kingg2
Registered User

Join Date: Apr 2009
Posts: 14
Time spent in forums: 4 h 28 m 48 sec
Reputation Power: 0
Numeric vs Float

Hello I have a quick question. I am thinking of applying float to replace numeric. The numeric precision that I am using is (18,9). What float precision would be equivalent to numeric(18,9)? Is float faster than numeric?

Thanks

#2
April 28th, 2009, 09:18 AM
 shammat
Contributing User

Join Date: Oct 2003
Location: Germany
Posts: 2,701
Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 27 m 57 sec
Reputation Power: 284
Quote:
 Originally Posted by kingg2 Hello I have a quick question. I am thinking of applying float to replace numeric. The numeric precision that I am using is (18,9). What float precision would be equivalent to numeric(18,9)?
There is no equivalten as float stores an approximation of the value you put into it.

From the manual:
Quote:
 Originally Posted by Manual If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.
The question is, why would you want to replace the exact storage of a number with a data type that will not store the values the same way as you put them in?

#3
April 28th, 2009, 09:47 AM
 kingg2
Registered User

Join Date: Apr 2009
Posts: 14
Time spent in forums: 4 h 28 m 48 sec
Reputation Power: 0
This does not pertain to a monetary value. The element in the table is for a lightcurve that has around 7 decimal places.

#4
April 28th, 2009, 02:01 PM
 shammat
Contributing User

Join Date: Oct 2003
Location: Germany
Posts: 2,701
Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 27 m 57 sec
Reputation Power: 284
Quote:
 Originally Posted by kingg2 This does not pertain to a monetary value. The element in the table is for a lightcurve that has around 7 decimal places.
Monetary is just an example.

If you can live with the fact that the values you put into the database might not be the same as those that you get out, then you can surely use float.

But still: what is the reasoning for wanting to change it?

#5
April 28th, 2009, 02:06 PM
 kingg2
Registered User

Join Date: Apr 2009
Posts: 14
Time spent in forums: 4 h 28 m 48 sec
Reputation Power: 0
I want a faster data type than numeric. Any suggestions?

#6
April 28th, 2009, 02:37 PM
 shammat
Contributing User

Join Date: Oct 2003
Location: Germany
Posts: 2,701
Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 27 m 57 sec
Reputation Power: 284
How do you know numeric is slow, and how would you test that a different one is faster?

#7
April 28th, 2009, 02:39 PM
 kingg2
Registered User

Join Date: Apr 2009
Posts: 14
Time spent in forums: 4 h 28 m 48 sec
Reputation Power: 0
I made a test table of 1,000 elements in one column. I then tested the select queries with the two different data types. After I would test one query I would copy the table and then drop the old table. I would then restart my postgres server to delete the cache. I did this 32 times for each data type.

#8
April 28th, 2009, 03:07 PM
 shammat
Contributing User

Join Date: Oct 2003
Location: Germany
Posts: 2,701
Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 27 m 57 sec
Reputation Power: 284
And the result showed that float was faster?
Interesting, I would not have expected that.

How much faster is it?

#9
April 28th, 2009, 03:09 PM
 kingg2
Registered User

Join Date: Apr 2009
Posts: 14
Time spent in forums: 4 h 28 m 48 sec
Reputation Power: 0
No, actually numeric was faster... by around 2 milliseconds.

#10
April 28th, 2009, 03:59 PM
 shammat
Contributing User

Join Date: Oct 2003
Location: Germany
Posts: 2,701
Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 27 m 57 sec
Reputation Power: 284
Quote:
 Originally Posted by kingg2 No, actually numeric was faster... by around 2 milliseconds.
So there is no reason at all to use a datatype which doesn't store accurate numbers.
2 milliseconds is within the range of the accuracy of the measurement anyway, so basically the two are equally "fast".

The only thing I can think of that might be faster (at least for data retrieval), would be to store the numbers as integers and do the conversion in the front end.

You'll need to define a max. number of fractional digits though - which you also need to do with numeric but not with float.

Theb you'd store e.g. 0.0000001 as 1, 0.000001 as 10, 0.00001 as 100 and so on.

#11
April 28th, 2009, 06:15 PM
 kingg2
Registered User

Join Date: Apr 2009
Posts: 14
Time spent in forums: 4 h 28 m 48 sec
Reputation Power: 0
Thank you very much for your help. It was a pleasure talking with you!

#12
July 28th, 2009, 12:25 AM
 wakerunner
Contributing User

Join Date: Nov 2008
Posts: 68
Time spent in forums: 12 h 49 m
Reputation Power: 5
This is something I discovered the hard way... after designing my database with double precision fields. I wish I would have searched more before settleing on the design. Why would anybody want to use a field that may or may not store the number you put into it? If you ever run a query on that field the results will be inconsistent, when there is a match it will not always be returned.

Quote:
 Originally Posted by shammat Monetary is just an example. If you can live with the fact that the values you put into the database might not be the same as those that you get out, then you can surely use float. But still: what is the reasoning for wanting to change it?

#13
January 11th, 2010, 09:47 AM
 GRFrones
Registered User

Join Date: Jan 2010
Posts: 1
Time spent in forums: 13 m 15 sec
Reputation Power: 0
There is an important conceptual difference between fixed point (numeric, integer, etc.) and floating point (float). Floating point is always an approximation, while fixed point is the exact value. However, the range of values you can store in a floating point field is much greater than fixed point.

For a number like 10^30, you would need a huge numeric field (and I don't think you can use numeric type in this case), but float should work.

In most business applications, there is no reason for using float, instead of numeric... however, in physics or statistics, for example, you can't use fixed point numbers.

Cheers.

 Viewing: Dev Shed Forums > Databases > PostgreSQL Help > Numeric vs Float