Help needed to use arrays in interpolation

[DjebbZ]

Hello,

I'm using sql-template-strings 2.2.2, pg 6.1.2 and pg-promise 5.5.2, on node v6.9.1. Red Hat Linux, connected to PostgreSQL 9.4.

I'd like to use a javascript array in an interpolation for an IN statement. Something like this :

sql`select something from somewhere where somefield in (${my_array})`

It seems it doesn't work. When I log the query object, it has this shape :

{ 
query: 'select something from somewhere where something in $1'
values: [ '{"item1","item2","item3"}' ] // WEIRD !
}

where my_array is ['item1', 'item2', 'item3'].

I tried a lot of voodoo/string concatenation, but of course they didn't work. It seems I'm missing something or arrays of string are not supported.

Any idea ?

Thanks in advance !

[vitaly-t]

pg-promise supports it nicely, via the :csv filter:

db.query('select something from somewhere where somefield in ($1:csv)', [arrayElement]);
// or:
db.query('select something from somewhere where somefield in (${data:csv})', {data: [1,2,3]});

[DjebbZ]

Thanks. What about :

sql`select something from somewhere where somefield in (${myArray}:csv)`

?

[felixfbecker]

@DjebbZ If you want to bind the array dynamically as a parameter (don't know the array length in advance), just use ANY instead of IN:

SQL`SELECT something FROM somewhere WHERE somefield = ANY ${myArray}`

IN requires a fixed list, you can only bind parameters inside the list, not the whole list itself. ANY can use a dynamic array.

@vitaly-t your solution does not work with prepared statements, nor is it related in any way to this module.

[vitaly-t]

your solution does not work with prepared statements

Of course it doesn't, as prepared statements are formatted by the server, by their very definition. And neither does yours ;)

nor is it related in any way to this module

It is related to the question.

[felixfbecker]

It does. The statement above will evaluate to

{
  text: 'SELECT something FROM somewhere WHERE somefield = ANY $1',
  values: [myArray]
}

[DjebbZ]

One. Thousand. Thanks. Saved my day !

It would be nice to document it somehow since arrays are ubiquituous in Javascript.

[Neamar]

For future references, if you need to do something more than IN and actually use postgres array, it can still be done.

SQL:

SELECT unnest($1::integer[]) as id

And the parameter to send in Javascript is this string:

`{${myarray.join(',')}}`

[herenickname]

@DjebbZ If you want to bind the array dynamically as a parameter (don't know the array length in advance), just use ANY instead of IN:

SQL`SELECT something FROM somewhere WHERE somefield = ANY ${myArray}`

IN requires a fixed list, you can only bind parameters inside the list, not the whole list itself. ANY can use a dynamic array.

@vitaly-t your solution does not work with prepared statements, nor is it related in any way to this module.

postgres_1 | 2018-12-30 17:50:51.425 UTC [35] ERROR: syntax error at or near "$1" at character 44 postgres_1 | 2018-12-30 17:50:51.425 UTC [35] STATEMENT:
postgres_1 | SELECT id FROM skins WHERE id = ANY $1 postgres_1 |
backend_1 | (node:28) UnhandledPromiseRejectionWarning: error: syntax error at or near "$1"

[geon]

@ekifox

You need parentheses around the list:

SQL`SELECT something FROM somewhere WHERE somefield = ANY (${myArray})`