Floating point numbers

[Itai Shirav]

Hi,

The documentation says that "we do not recommend storing floating-point numbers in tables."

Can you explain why?

Are integers better for compression?

Thanks

[Robert]

CPU process non-float number faster. 

I store float numbers as integers (just multiply it before insert and divide before printing)

[man...@gmail.com]

There is no real problems of storing floats in tables.
We are doing it in many places...
Example: we store data of Graphite (monitoring system) in ClickHouse, and value of metric is stored in column of type Float64. It works fine for that purpose.

But there are some considerations:

1. Using floats for money is discouraged.

2. When there is value with fixed precision (for example, page loading time in milliseconds), it's much easier and efficient to store it as integer (as number of milliseconds in this example).

3. Parsing of floats from text formats is inexact: not closest machine representable number is parsed, but with some little rounding error.

4. Comparing results of different calculations in floats are more difficult. Example: SELECT 1 - 0.9 = 0.1 returns 0.

5. Floats are more difficult by existence of infs, nans, negative zero. Note that in standard SQL these values are prohibited in FLOAT, REAL data types, but in ClickHouse, Float64 and Float32 are machine floats.

6. Result of calculation may depend on calculation method (what kind of CPU registers and instructions was used), that is implementation specific.