Malformed array literal

Queries and Resources
1

Trying to pass values from a multiselect component value (array) postgres array field using the gui mode and getting a Malformed array literal error. The .value indicates it's a array but its barfing on the update.

Using a sql statement as such no issues.
update itemtype
set uses = {{multiselectuses.value}}
where id = {{itemtypeupdate_id.value}}

malformed
malformed3724×1180 280 KB

2

Hey @Dhof — thanks for posting and welcome to the forum!

This is a non-obvious issue with the way Postgres expects array syntax and the default way Retool handles arrays in Javascript.

In order to update a Postgres array column, you'll need to transform the JS array to a Postgres array, doing something like this:

{ {{ multiselect.value.map(x => `"${x}"`).join(",") }} }

Here's a working example (this is double confusing because we display the array in the table like Javascript, but Postgres requires a different format):

image
image2416×1802 240 KB

2 Likes
3

what about the case where the id is in an array and you want to SET all the IDs in the array to {{table1.selectedRow.dat.id? Basically the opposite of your solution. I tried it but i got an error "Condition WHERE ... id IN (...) failed because the value is not an array.)

I tried it in sql mode with the following:

update admin.node 
set bundle_id = {{tableBundles.selectedRow.data.id}}
WHERE admin.node.id in {{ multiselectNodesAvailable.value.map(x => `${x}`).join(",") }}

The output from this is "101005,101006"

I tried this:

update admin.node 
set bundle_id = {{tableBundles.selectedRow.data.id}}
WHERE admin.node.id IN { {{ multiselectNodesAvailable.value.map(x => `${x}`).join(",") }} }

Error: syntax error at or near "$2"

I've been working on this all day so I'd really appreciate some help.

4

Hey @leclep — I believe that Postgres syntax is a bit different here again. Instead of where column in {array}, Postgres requires where column = ANY ({array}). The key part here is the = ANY () comparison operator.

So I think you would need to update your query to:

update admin.node 
set bundle_id = {{tableBundles.selectedRow.data.id}}
WHERE admin.node.id = ANY ( { {{ multiselectNodesAvailable.value.map(x => `${x}`).join(",") }} } )

More info on the ANY operator:

Another option could be to the Bulk Update option in the GUI Mode for Postgres queries, which might be a lot easier. If you go this route, you only need to supply a JS array in the changeset, so you don't need to do the .map().join() business.

More info on the GUI mode for Postgres queries in our docs:
https://docs.retool.com/docs/sql-writes#bulk-updates

1 Like
5

This is an issue with the GUI / prepared statements but not with custom SQL.

Y'all should consider updating your ORM layer to handle this for postgres users (rather than have everyone using postgres have to do this themselves every time).

1 Like