I have a Multiselect component and I want to store the results into a Postgres text[] array column. I can
get the record inserted but the results are not quite right.
This is the result I expect when running the query from pgAdmin:
insert into todo (todo, assigned) values ('Paint House', ARRAY ['Brad', 'Laurie', 'Tristan'])
// assigned (correct): {Brad,Laurie,Tristan}
Here are the two version I have so far
insert into todo (todo, assigned) values ({{txtNewToDo.value}}, ARRAY [{{msAssigned.value}}])
// assigned: {"{\"Brad\"","\"Laurie\"","\"Tristan\"}"}
The second:
insert into todo (todo, assigned) values ({{txtNewToDo.value}}, ARRAY [{{msAssigned.value.join(",")}}])
assigned: {"Brad,Laurie,Tristan"}
And the third using a transformer
// trFormatAssigned transformer
return "'" + {{msAssigned.value}}.join("','") + "'";
insert into todo (todo, assigned) values ({{txtNewToDo.value}}, ARRAY [{{trFormatAssigned.value}}])
assigned: {"'Brad','Laurie','Tristan'"}
I also tried string_to_array as in from pgAdmin:
insert into todo (todo, assigned) values ('Paint House', string_to_array('Brad,Laurie,Tristan',','))
// assigned (correct): {Brad,Laurie,Tristan}
And from Retool:
insert into todo (todo, assigned) values ({{txtNewToDo.value}}, string_to_array({{msAssigned.value}},','))
// assigned: {"{\"Tristan\"","\"Joren\"","\"Paul\"}"}