What is the difference between NUMERIC and FLOAT in BigQuery?

Question

I read the docs: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types https://cloud.google.com/bigquery/pricing#data

I know that FLOAT is 8 bytes while NUMERIC is 16 bytes Is that the only difference? The docs says that NUMERIC can range -99999999999999999999999999999.999999999 to 99999999999999999999999999999.999999999 but it doesn't specify the range for FLOAT.

Answer 1

I like the current answers. I want to add this as a proof of why NUMERIC is necessary:

SELECT 
  4.35 * 100 a_float
  , CAST(4.35 AS NUMERIC) * 100 a_numeric

This is not a bug - this is exactly how the IEEE defines floats should be handled. Meanwhile NUMERIC exhibits behavior closer to what humans expect.

For another proof of NUMERIC usefulness, this answer shows how NUMERIC can handle numbers too big for JavaScript to normally handle.

Before you blame BigQuery for this problem, you can check that most other programming languages will do the same. Python, for example:

Answer 2

There are quite a few differences:

• Range: FLOAT can be get as big as ±2^1023 (although it can't represent every integer in this range)

• Precision near zero: near zero, FLOAT can be as precise as 2^-1022.

• NaN/Inf: FLOAT has "not a number" and positive and negative "infinite" values, while NUMERIC does not.

• Storage size: each NUMERIC requires 16 bytes storage, but FLOAT only requires 8 bytes.

• Unpredictable nature: As Ajay hints at, the big drawback to FLOAT is that the numbers that it can precisely represent don't really align that closely with the numbers we are typically interested in. As a result, floating point should be used for physical quantities where some error is acceptable. If you require predictable, exact human-predictable results, like in financial calculations, use NUMERIC

Answer 3

The main difference is Floats / Doubles are binary floating point types and a Numeric will store the value as a floating decimal point type. So Numeric have much higher precision and are usually used within monetary (financial) applications that require a high degree of accuracy. But in performance wise Numeric are slower than double and float types.Numeric can 100% accurately represent any number within the precision of the decimal format, whereas Float and Double, cannot accurately represent all numbers, even numbers that are within their respective formats precision.