Value of type FLOAT64 cannot be assigned to field, which has type BIGNUMERIC

App Building
1

I'm trying to build an app that will update records in Big Query.
I'm using the number input button to get a replacement value.
If I use a whole number like 3, it will update. But if I enter a decimal I get an error:

'Value of type FLOAT64 cannot be assigned to field, which has type BIGNUMERIC'

How would I get this to work with decimals?

thanks!

2

Hey @James_76! Hmm interesting.

Would using CAST work for you? https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions

Something like:

UPDATE test.numericcol SET testcol = CAST(5.1 AS BIGNUMERIC) where id = 1;

3

Hi,
Thanks for the reply!
I was trying to use the GUI to do the update, and I could not find a way to apply a 'cast' to the data, or modify the type using .js

I eventually did as you suggest and wrote the SQL with a cast statement.

However, I am now trying to do a bulk insert from csv and have run into the same issue where the types don't align with Bignumeric...is there a way to manage that at GUI level?

5

We have a feature request to support passing decimal values to NUMERIC/BIGNUMERIC type columns in GUI mode!

In the meantime, your best bet is SQL mode. If transforming your data with JS works, then you can always use a JS transformer type query to do so before passing the data into your update query.

6

@victoria checking in here as this may be related: bq NUMERIC data type is not supported by Retool on query output, it renders as string.
Workaround here too is to cast to float (in the SQL statement) but this is not ideal.

Is there a feature request to support all types ?

7

Hi @yiga2 Thanks for checking in! The feature request that we're tracking here is for properly supporting numeric and bignumeric columns. It hasn't been picked up yet, but I'll let the team know you checked in

Are there other types that you've found we need better support for?

8

JSON would be nice, especially with expandable rows being introduced. But this may probably require a new column type.

Our use case: we show invoice header data in a table, and have a separate table that shows the line items upon clicking the row of the first table.

Ideally we would pass the line details in a json that would be parsed and rendered in a child table when expanding the header/parent row.

That would save a round trip to the database as we could pull all in one query.

EDIT:
My comment above is redundant after reading the documentation on list views as arrays are supported in Retool, and can be used in expandable/nested lists/tables. One can use JSON.parse() to parse a json-string of arrays (invoice lines for us). As such, BiqQuery's ARRAY of repeatable rows can be consumed with little transformation. This is great !

1 Like