Wrong Escaping of array in Postgres

[kayoub5]

Latest version of knex (0.12.3) does not escape array of strings correctly:

var assert = require('assert');
var pg = require('knex')({client: 'pg'});

var sql = pg.raw('SELECT ?::text[]', [['foo', 'bar']]).toString();
console.log(sql); // "SELECT '{'foo','bar'}'::text[]"
assert.equal(sql, "SELECT '{''foo'', ''bar''}'::text[]");

[xizhao]

+1, we're having this problem as well.

[megavoltua]

Seems like array of strings is not applicable for WHERE IN:

knex.raw('SELECT * FROM docs WHERE type IN (?::text[])', [['account']]);

Produces

SELECT * FROM docs WHERE type IN ('{"account"}'::text[])

When running in Postgres 9.4:

Unhandled rejection error: SELECT * FROM docs WHERE type IN ($1::text[]) - operator does not exist: text = text[]

[elhigu]

@megavoltua what should be correct SQL in your case? Why are you using knex raw there instead of .whereIn ? Looks like you are putting an array inside of array, which would explain type error postgres is throwing...

knex('docs').whereIn('type', ['account']);

[megavoltua]

@elhigu Yes, I know I can put .whereIn, but knex.raw is used widely in my current project and I must support it...

So I'm wondering, is there a way to use array binding for raw where in?

[elhigu]

@megavoltua knex doesn't know context of SQL string where replacement is being done, so array is always replaced with '{ "item" }' format.

I can think of two options if you really cannot avoid this situation (I have needed this maybe once during the last 3 years and probably it was unnecessary even then):

Unroll your string array to (?,?,?,?) in raw string and pass every item separately so you would end up with:

knex.raw('SELECT * FROM docs WHERE type IN (?)', ['account']);

or for bigger table something like:

knex.raw('SELECT * FROM docs WHERE type IN (?,?,?)', ['account','foo','bar']);

Or you can use textColumn = ANY(array) instead of where in which actually works for postgres array type:

knex.raw('SELECT * FROM docs WHERE type = ANY(?::text[])', [['account']]);

[megavoltua]

@elhigu I think ANY(array) is the best solution here, I forgot about it 😄
Thanks!

[TangMonk]

How to update array?

[elhigu]

@TangMonk What is the raw SQL query you are trying to write to make your array update?

[TangMonk]

@elhigu

like this sql:

update users set agents_array = ARRAY[1,2,3]::text[];

How to do this in knex?

[elhigu]

something like this:

knex('users').update({ 
  agents_array: knex.raw(`ARRAY[${arrayItems.map(() => '?').join(',')}]::text[]`, arrayItems)
}).then(res => console.log('results', res));

[TangMonk]

@elhigu thank you!