#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    14
    Rep 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. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    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:
    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?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    14
    Rep 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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    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?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    14
    Rep Power
    0
    I want a faster data type than numeric. Any suggestions?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    How do you know numeric is slow, and how would you test that a different one is faster?
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    14
    Rep 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.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    And the result showed that float was faster?
    Interesting, I would not have expected that.

    How much faster is it?
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    14
    Rep Power
    0
    No, actually numeric was faster... by around 2 milliseconds.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    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.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    14
    Rep Power
    0
    Thank you very much for your help. It was a pleasure talking with you!
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    72
    Rep Power
    6
    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.




    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?
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    1
    Rep 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.

IMN logo majestic logo threadwatch logo seochat tools logo