I am querying my table from a postgres database. Upon attempting to edit an array (in this case the column ‘h’ is an array e.g. [“Janknkfnweknfjwnf fvwvew”]) and saving the changes using the ‘Bulk update via a primary key’ action type, I face this error of ‘malformed array literal’. Any suggestions on what are the steps I can take to resolve this error?
HI @hayley and welcome to the forum!
What is your database column's type? From my personal experience I use jsonb for these kind of arrays.
In your retool table, what type is the column where the array is? It could be that it is passing it as a string because it's a text one (opposed to tag/tags)
Hi @MiguelOrtiz
Not quite sure if this is what you are asking for but my database column's type is ARRAY (refer to picture below)
whereas the column type on my retool table is 'Auto' (refer to picture below)
Thank you @hayley,
I'm not a PostgreSQL expert, but I'm thinking the following could help to troubleshoot:
- The array column is expecting the insert value to be either between curly brackets {Janknkfnweknfjwnf fvwvew} or with the ARRAY constructor, ARRAY [['Janknkfnweknfjwnf fvwvew']]
- I can see you're using the legacy table. As you are using auto, can you confirm whether your column h values are a string or an array by seeing the table data source on state?
Hello @MiguelOrtiz! Really appreciate all your help in giving me some ideas on how to troubleshoot! I have successfully solved this issue, it could not have be possible without your help.
Here's what I have done (For anyone who faces the same issues):
- Convert the target column into JSON type
- Create a transformer to parse it into an array
- Create a bulk update query so that the changes can be made
Do note that it will not work if you are using a legacy table. Be sure to use a new table before carrying out the above steps! Hope this helps 
3 Likes
Very nice, well done. Glad you found a solution!