Specifying decimal precision in BigQuery-CodePudding

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

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

CodePudding user response：

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()

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.