Hello,
Preliminaries
- Skill level: new to Retool, familiar with other similar frameworks.
- DB: Docker Postgres/latest.
- Retool versions tested: Web and Self Hosted.
- What works: the following
SELECT
statement.
SELECT UNNEST ( ARRAY[1, 3, 5] ) AS ARRAY;
Query ran successfully
ARRAY
-------
1
-------
2
-------
3
-------
- What does not work: when using the
Multiselect
component, inserting `{{multiselect.value}}' into a SELECT statement, the array is expanded to a string, as opposed to an array of integers, resulting in a parsing failure(?).
Details
Given the following table structure:
game=> SELECT * FROM lkp_tbl_expansions;
pk_expansion_id | expansion_name
-----------------+--------------------
1 | Core
2 | In the Lab
3 | On the Brink
4 | State of Emergency
(4 rows)
I created a Multiselect
component named expansions
with the following configuration:
When creating a query, the {{expansions.value}}
expands to a string, instead of an array of integers.
Query preview shows the following:
SELECT UNNEST ( ARRAY[{{expansions.value}}] ) AS ARRAY;
SQL Prepared Statement
SELECT UNNEST ( ARRAY[$1] ) AS ARRAY;
$1 =
[
1,
3
]
Query ran successfully
ARRAY
-------
{"1","3"}
-------
When attempting an INSERT
of the array values, into another table, Postgres returns the following error: column "column_name" is of type integer but expression is of type text.
When I tried to perform the same task using the Appsmith framework, it worked as expected. When attempting an INSERT
using another query, it succeeds without an issue.
Iām not sure what Iām doing wrong , any help would be appreciated and thanked in advance.
Josie.