24

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Luis
  • 1,305
  • 5
  • 19
  • 46

3 Answers3

29

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

enter image description here

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:

enter image description here

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • The question is if I'm working only with 3 digits after dot. float is good enough? – Luis Feb 21 '19 at 09:17
  • 1
    It doesn't matter how many digits after dot. See the example above - 4.35 has only 2, but some mathematical operations can screw its representation. Still, float is good enough for most uses, even tho sometimes it can surprise you. – Felipe Hoffa Feb 21 '19 at 17:00
  • 16 bytes is good enough for a precision of 1/1000, and i think up to 10^-10. 64 bit floats usually have 53 manissa bits and 11 exponent bits. so worst case scenario, a number between 2^10 to 2^11 can be accurately represented up to a multiple of 2^(11-53) = 2^-42. and that's about 10^-10. someone please correct me if i'm wrong. – william_grisaitis Jun 28 '23 at 18:03
9

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

David
  • 9,288
  • 1
  • 20
  • 52
7

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.

Ajay Kharade
  • 1,469
  • 1
  • 17
  • 31