Bulk Insert into BigQuery Error Value has type FLOAT64 which cannot be inserted into column, which has type NUMERIC

I’m doing a bulk insert into a BigQuery table using a BigQuery resource. I have a currency amount, that is represented as “amount” with datatype “NUMERIC” in my BigQuery table schema.

When I run my Retool BigQuery bulk insert query, I get the following error: “Value has type FLOAT64 which cannot be inserted into column amount, which has type NUMERIC at [1:335]”

I have parsed this string out of a table using Number() but have also tried parseFloat(). AFAIK Numeric specifically supports FLOAT64. Is there something I’m missing? Is there some issue with Legacy vs. Standard SQL?

Hi @jargetz! Welcome to the community! :sunglasses:

Are you able to explicitly cast this value to the NUMERIC type? Might look something like this:

1 Like

Thanks Ben! I’m using a Bulk Insert Query so I don’t believe I have the option to cast. I will try writing an intermediary step that generates raw SQL with casting for my entire array of rows and then using that in SQL Mode.

However, if I write a single Insert in SQL Mode without casting, it succeeds, which made me a bit suspicious it had something to do with bulk mode.

Hmmm interesting!

Could you share a screenshot of your query and we can take a second look?