Parameter types must be provided for null values via the 'types' field in query options

any updates on this? Thanks

Asked the team that works on resources! Will let you know once I hear back.

1 Like

No updates yet, but I bumped this request again :crossed_fingers:

2 Likes

I happened to have the same issue. There are two workaround you can try. See this thread. How do I update an optional empty null field to BigQuery?

1 Like

We are facing the same issue here, which is basically stopping us from using GUI writes with bigquery. Any updates on this request?

Checking in with the team to see if they have any updates. Thank you for asking about this, @DATA_THATMEMORY and @Tommy_Romano!

I'm encountering the same issue.

@victoria - Do you know if there is any solution to this issue?

1 Like

This is currently blocked on the Retool side, but we've bumped it to the team in hopes of a resolution soon :crossed_fingers: I'm sorry about the delay here and will keep this thread updated!

As a quick note, something like this should work in SQL Mode !

update test2.users 
set first_name = {{ {RT$BQTYPE: 'STRING', value: null} }} 
where id = "2"

How can apply this in GUI mode?
image

When I do like this it shows the same issue.

parameter types must be provided....

Also how should I pass my query column name in place of null
Example

MERGE bqtable1 AS a
USING UNNEST({{table16.recordUpdates }}) AS b
ON a.uuid = b.uuid
WHEN MATCHED THEN
UPDATE
SET
a.address = {{ {RT$BQTYPE: 'STRING', value: b.address} }}

Anyway, I tried to implement above logic in all possible ways. But that's not working for me. Maybe it on small scale like above, but in most common cases like when we try to update the table or insert new values this doesn't works.

And this issue is really kind of annoying as we have to fill our tables with empty strings ("") instead of null. That creates confusion while working on BigQuery console, whether to use is null or to use length(column) = 0, Also it requires to put additional logic on retool. From last 7 months we're dealing with this issue.

Is it too difficult to resolve this?

I think instead of making retool's GUI more intuitive please figure out this null issue as it's becoming very painful. and that's actually the base of whole retool app.

I hear you! I let the engineer in charge know.

Thank you everyone for the feedback, +1s and context shared; this truly would not have been prioritized without all of it.

I may have good news to share soon :eyes:

I'm also waiting for a good news on that issue. :slight_smile:

+1 on this getting resolved. New user for Retool and this is a real bummer

Hello everyone! I have exciting news. We've updated the error message to include the correct syntax. :tada: :eyes: :tada:

{{ {RT$BQ_TYPE: 'STRING', value: null} }}

Error:

This one is a super cumbersome workaround which results in gray hairs. There's no reason why NULL's should not be supported (that's just weird). Hope you guys can fix the issue natively.

Definitely valid. Noted internally!

How to insert empty array into a field when using bulk insert?

TL;DR

I'm getting the same error when trying to insert empty array values to a field. I've tried everything: [], {RT$BQ_TYPE: 'ARRAY', value: null}, {RT$BQ_TYPE: 'ARRAY', value: []}, {RT$BQ_TYPE: 'STRING', value: null} - nothing works. I've tried in SQL mode as well as GUI mode. What to do?

Hey @merualbert! Would you mind sharing a screenshot of your current query? :slight_smile: