WHERE col IN Query with empty array as parameter

Question

From example where-col-in example and this answer, WHERE IN clauses should have query with parameters with following syntax

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

where data is an array.

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

 SELECT * FROM users WHERE id IN ()

which is a syntax error.

Consider following statements:

• this 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 reported for squel library has answers on how knex and ruby's sequel behaves in such scenario.

Is this a bug or am I doing something wrong? Could there be an alternate syntax which works for both scenarios.

---

For instance, an alternate query using ANY works for both situations:

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

What should be the best way to have WHERE col IN queries which could also handle empty arrays as params?

Cahier

See this issue: github.com/vitaly-t/pg-promise/issues/442 – Ornery

Answer 1

Common Answer

Is this a bug or am I doing something wrong?

Not a bug, but a flaw for most SQL frameworks. It is very difficult to handle such parameters, so most frameworks just leave the empty list as it is to generate invalid SQL XXX in ().

Could there be an alternate syntax which works for both scenarios.

A simple approach is:

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

What about knex or sequel?

They are Query Builder frameworks, so they have chances to generate special SQL to handle empty lists. Popular methods used by Query Builder frameworks to handle WHERE id in () OR ...:

• WHERE (id!=id) OR ...
• WHERE (1=0) OR ...
• WHERE (1!=1) OR ...
• WHERE false OR ...
• etc

Personally I do not like id!=id :)

For Some Framework

You may check its manual to see if there is some way to handle empty lists, eg: can the framework replace the empty list with a non-existing value?

Joselynjoseph

Answer 2

Library pg-promise gives you complete freedom in generating any SQL you want, it does not validate or control it in any way, as it is not an ORM.

CSV Filter is generic, it can be used in various context for generating queries. So when you are using it specifically for IN ($1:csv), it doesn't know it, and produces again generic output.

As per the comments I left in this issue, the right approach is to check whether you have any data in your array, and if not - do not execute the query at all. First, the query would be invalid, and even if you patch it with some empty logic, that means it won't generate any result, and executing such a query becomes a waste of IO.

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

Ornery