How to edit array and save changes on my postgres database in retool?

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)
Screenshot 2024-07-04 at 12.20.51 PM

whereas the column type on my retool table is 'Auto' (refer to picture below)
Screenshot 2024-07-04 at 12.21.14 PM

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):

  1. Convert the target column into JSON type
    image
  2. Create a transformer to parse it into an array
  3. 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 :slight_smile:

3 Likes

Very nice, well done. Glad you found a solution!