I've been having a really hard time trying to figure out how to get an array with dynamic length into a parameterized query in the pg nodejs library. I've tried several different things but none of them seem to be working.
For instance, here is an example query object that I'm trying to use:
{
text: 'INSERT INTO schema.table (userid,name,columns,sort,notification) VALUES ($1)',
values: [ ['testuser', 'testname', null, null, 'on'] ]
}
I've read in the docs that I can use the $1 character as a kind of wildcard and it should be able to dynamically now the amount of values in the array. But this never seems to work, I always get back the following error when running client.query(query.text, query.values:
error: INSERT has more target columns than expressions
Even though it can be clearly seen that there are 5 columns and 5 values. I've tried using $1::TEXT[] as well but that hasn't worked either still the same error.
According to this post the method I have been trying should work but it just hasn't been.
Does anyone know how I can get around this? I need to use parameterized queries so that pg will escape and sanitize the data as often times it will come from user input. I could do something cheeky like value.map((col, i) => "$"+(i+1)).join(","); and use that for the placeholders but I really don't want to, and there's places in my code it won't really work because of the way I'm dynamically forming a lot of my queries.
Any help is appreciated, thank you.
hi anyone looking at this in the future, just use pg-format https://www.npmjs.com/package/pg-format