WHERE col IN Query with an empty array as a parameter

Question

From example where-col-in sample and this answer , WHERE INyou must have a parameter query with the following syntax

const response = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])

where data is an array.

Now that the data is an empty array, it produces the following query

 SELECT * FROM users WHERE id IN ()

which is a syntax error.

Consider the following statements:

it works

const x = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [[1, 2, 3]]);

this does not work

const y = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [[]]);

A similar error is reported for the library squel, it has the answers to how knexand ruby sequelbehave in such a scenario.

Is this a mistake or am I doing something wrong? Could there be an alternative syntax that works for both scenarios.

, ANY :

await db.any(`SELECT * FROM table WHERE id = ANY($1)`, [[1, 2, 3]]);
await db.any(`SELECT * FROM table WHERE id = ANY($1)`, [[]]);

WHERE col IN , ?

+6

2

pg- SQL, , , ORM.

CSV Filter , . , IN ($1:csv), .

, , - , - , - . -, , , , , IO.

let result = [];
if (data.length) {
    result = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data]);
}
/* else: do nothing */

+4

vitaly-t 09 . '18 7:52

shawn · Accepted Answer · 2018-03-09T07:26:42+0000

- ?

, SQL. , , SQL XXX in ().

, .

:

if(data is empty) data = [ -1 ]   //fill a non-existing id
db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])

knex sequel?

Query Builder, SQL . , Query Builder WHERE id in () OR ...:

id!=id:)

, , , : ?