WHERE col IN Query with empty array as parameter

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 */

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?