Specifying decimal precision in BigQuery

David542 0
Viewed 66

Is there a way to cast to a specific decimal precision in BigQuery, or how is this usually done?

with tbl as (
  select 1.0 as fx_rate
) select cast(fx_rate as decimal(10,5)) from tbl

Parameterized types are not allowed in CAST expressions.

And it seems to only allow it as an inferred decimal, which I believe here is just DECIMAL(1):

with tbl as (
  select 1.0 as fx_rate
) select cast(fx_rate as decimal) from tbl

enter image description here

For example, in Postgres (and derivatives) I can do:

enter image description here

google-bigquery sql
1 Answers

As suggested by @nbk, you may use the below to generate decimals (including leading zeros based your sample data) and then use a string function to specify desired precision. However, using FORMAT() function can only accomodate up to 6 decimal places only. Also, your results will be in STRING.

with tbl as (
  select 1.0 as fx_rate
)
select substr(FORMAT('%F', fx_rate),1,4) from tbl

Output:

enter image description here

Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future.

Feel free to edit this answer for additional information.

userMTlc10
Related