What is the difference between NUMERIC and FLOAT in BigQuery?

sql google-bigquery
Luis picture Luis · Feb 20, 2019 · Viewed 11.1k times · Source

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

Felipe Hoffa picture Felipe Hoffa · Feb 20, 2019

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

Related questions

Cannot access field in Big Query with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>>

I'm trying to run a query using Standard SQL Dialect (ie not Legacy SQL) on BigQuery. My query is: SELECT date, hits.referer FROM `refresh.ga_sessions_xxxxxx*` LIMIT 1000 But keep getting the error Error: Cannot access field referer on …

Read More
BigQuery SQL WHERE Date Between Current Date and -15 Days

I am trying to code the following condition in the WHERE clause of SQL in BigQuery, but I am having difficulty with the syntax, specifically date math: WHERE date_column between current_date() and current_date() - 15 days This seems …

Read More
How can I extract date from epoch time in BigQuery SQL

I have date stored in Epoch Time and I want to extract Date from it. I tried the code below and I get null as output. date_add( (timestamp( Hp.ASSIGN_TIME)), 1970-01-01,"second" ) as Extracted_date_Epoch Ex …

Read More