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

There is no equivalten as float stores an approximation of the value you put into it.

From the manual:
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?

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?

I want a faster data type than numeric. Any suggestions?

How do you know numeric is slow, and how would you test that a different one is faster?

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.

And the result showed that float was faster?
Interesting, I would not have expected that.

How much faster is it?

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.

Thank you very much for your help. It was a pleasure talking with you!

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.

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.