Hi,
I am using the Retool Postgres Database, but I don't seem to be able to set any of my fields as an array of the types offered. (I need one of my fields to be an array of text.) Is this feature missing in the Retool databases? Or am I just misunderstanding how to implement it.
Thanks in advance for the help.
Some more specifics and further oddities --
When I try to enter array type data into the 'text' field i.e. {home, office, other} and then attempt to select from the data with SQL, I get this error:
ERROR: cannot subscript type text because it does not support subscripting
However -- I used an external tool connected to my Retool PostGres database (Navicat). In Navicat, I attempted to change the field type to a text array i.e. text[]. It seemed in Navicat that the change didn't work. (It simply registers as "text" again.) But, when I go back to my table in Retool, the type (which used to be "text") is now "BOOL". And now, I can use the SQL query to retrieve elements from my array -- both in Navicat and in Retool Workflow. For example: "SELECT address_array[1] FROM tbl_cust_details;" returns the correct result, as if an array is being processed properly.
Is this expected behavior? Bugs?
Perhaps the Database isn't really ready for real use?
Here is the display from the field. Notice the "BOOL"
1 Like
Bump.
Hey @warlock - Retool DB doesn't yet support arrays, just added you to the request to bake those in!
1 Like
is there an ETA for this?
We don't have an ETA on our side with other development priorities at the moment, but this should really only be a limitation on creating new array columns in the UI. It is still just a Postgres DB behind the scenes, so you can always manually add array columns with something like ALTER TABLE table_name ADD COLUMN column_name text[]; and use the columns however you need in your apps. It also looks like once created, we still load the JSON editor in the UI which seems to function correctly with those data types.
thanks, given it's a new project we just switched to the json datatype. thanks for the update
still no ETA or update? like you said, it's really just a GUI thing but it would be kinda nice to have since in workflows the Retool AI Chat Completion resource requires a message history you either have to pass as input to the workflow or retrieve from a db... with the retool db so easy to access and sample tables filled out, it's handy to use and easy to switch to production with.
idk if it'd be easier, but you could just put a place in the GUI to enter SQL statements like the one you give so an external tool wouldn't be necessary (same outcome, different route 
)
Hey @bobthebear! Just for clarification, you can run the SQL statement that @jmann mentioned from inside Retool, so you don't need an external tool to make this work. But I've pinged our internal ticket as there is no update yet. We'll definitely let you know here when we have movement there. Thanks!
o.O would you mind showing me where? I'm probably staring right at it knowing me though 
Definitely! In any Retool app you can select Retool Database as a query resource. If you are in SQL Mode, you can write any Postgres acceptable SQL statement. For example, here's a table I created with a column that handles arrays.
But you could also update tables in a manner similar to the SQL shared above if you wanted to use an existing table versus creating a new one.
ohhh I see, I thought you meant on this screen it was possible:
here is where it'd be nice. so while I'm scrolling around the db I can update/create/delete stuff from this screen and I dont have to either switch tabs and make a temp resource like your screenshot or load up a tool to do it.
ok i see this is going to be confusing lol. using a command/query to create stuff or something would just be handy in cases like this where the UI doesn't support something yet
1 Like
Right, the internal ticket will be to add this functionality to the UI. Agreed that is where the functionality should live. But in the interim, you can do this all in Retool writing raw SQL against the Retool DB. I'll add a feature request to have a SQL editor on that page so that you don't have to switch tabs 
.
thank you! feel free to put "he's often lazy" in that request 
1 Like
Hahaha, not at all! Makes sense to have a SQL console there for things that aren't possible in the UI as you mentioned. Making that screen a 1 stop shop for all things Retool DB. I've defaulted to a Retool App that I call Retool DB Admin where I make any updates / changes like that, but in reality that's functionally not that different than linking an external tool, so I appreciate the nuance!
2 Likes