CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquerynumericsqldatatypes
testuserx
testuserx

Reputation: 226

BigQuery: Numeric DataType: Cannot store more that 19 digits

Why is that the column with NUMERIC dataType in BigQuery is unable to store this number: 12345678901234567890 [20 Digits].

The official documents say - it can store decimal values with 38 decimal digits of precision and 9 decimal digits of scale.

I've tried the following in a column which has NUMERIC dataType:

Is this a bug from bigQuery end? Will I have to needlessly apply ".0" for every number greater than 19 digits.

Link to official document: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

Screencap of error when I insert number with length 20 in column of dataType NUMERIC. enter image description here

Upvotes: 0

Views: 5590

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

If you use just 12345678901234567890 BigQUery consider this as a INT64 data type thus an error

You need somehow to tell BQ engine that this is not an integer but rather float or numeric
The simplest way to do so is to use 12345678901234567890.0 - in this case BQ will treat this as FLOAT64. If you need make sure it is NUMERIC you can for example explicitly cast it CAST(12345678901234567890.0 as NUMERIC)

See also example below 

#standardSQL
SELECT 
  12345678901234567890.0 float_value_a, 
  CAST(12345678901234567890.0 AS NUMERIC) numeric_value_b,
  CAST('12345678901234567890' AS NUMERIC) numeric_value_c, 
  CAST('12345678901234567890' AS FlOAT64) float_value_d

with result 

Row float_value_a           numeric_value_b         numeric_value_c         float_value_d    
1   1.2345678901234567E19   12345678901234567890    12345678901234567890    1.2345678901234567E19

Upvotes: 3

Related Questions