I want to be able to change order position of a lists table. Each list has it’s own position field. As I update list order I want to save this to the database. So I pass an array with the lists id field in the correct order and use a middly complex query to update the list:
with pos as (
select
id as pos_id,
row_number() over() as position
from (
select unnest(
array[36, 33, 35, 37, 46, 39, 40, 41, 43, 45, 38, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 58, 59, 60, 61, 62, 65, 66, 67, 68, 78, 79, 82, 83]
) as id
) a
)
update lists l
set position = pos.position
from
pos
where
l.id = pos.pos_id
This works perfectly in pg_admin but when I try to put this in Node it just won’t work.
I’ve tried to send $1 parameter using these forms:
- unnest($1)
- unnest($1::bigint[])
- unnest($1:list)
- unnest(ARRAY[$1])
- unnest(ARRAY[$1::bigint[])
- unnest(ARRAY[$1::list)
- unnest(ARRAY[$1::bigint[])
- unnest(ARRAY[‘ + positions.join(‘, ‘) + ‘])
- unnest(‘{‘ + positions.join(‘, ‘) + ‘}’)
- unnest(‘{‘ + positions.join(‘, ‘) + ‘}’::bigint[])
None worked. Some of them return a parse error, some a “need to add explicit type casts” error. How do I pass an array to pool.query in this case ?