Update single element in Postgres array column

Queries and Resources
1

I have the following query:

Update routing
set leg_lines[{{index}}] = {{lines}}
where route_id = {{selRoutes.selectedItem.route_id}}

It returns an error: array subscript in assignment must not be null

If I set the index manually (leg_lines[1]) then it works, so I assume this is incompatible with Retool's parameterizing the query.

Anyone have a Retool only workaround for this? I can obviously use a stored procedure.

2

I also suspected that passing in a String or Number injected inside of the array selection is not valid in parameterized queries since it would be the SQL language syntax instead of an individual value to check against. However, testing with a simple selection it doesn't seem like the dynamic value is the issue here:

1998×1016 191 KB

Does using some syntax like this work? If I remove the parenthesis around ARRAY[0,1,2,3,4] it will throw a syntax error

3

Hmmm, I do not see how just adding the parens gets it work in a SELECT query. Still not working in the UPDATE query though, get syntax errors in various places.

Tried various versions from just adding the parens, assuming ARRAY(1,2,3,4) is a stand in for my array column:

Update routing
  set (leg_lines[{{index}}]) = {{lines}}
  where route_id = {{selRoutes.selectedItem.route_id}});

Update routing
  set (leg_lines)[{{index}}] = {{lines}}
  where route_id = {{selRoutes.selectedItem.route_id}});

I also to inserting the whole ARRAY function in case this whole thing was a back door way of passing the index (pg arrays are 1 based):

Update routing
  set leg_lines[(ARRAY[1,2,3])[{{index}}]] = {{lines}}
  where route_id = {{selRoutes.selectedItem.route_id}});
4

Hey @bradlymathews! Puzzling :thinking: Out of curiosity, how are you passing in your {{ index }} and {{ lines }} values? Are they confirmed to be integers that match the values you've tested hardcoding with?

5