Menu
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?
442
To check if an array is null, use equal to operator and check if array is equal to the value null. In the following example, we will initialize an integer array with null. And then use equal to comparison operator in an If Else statement to check if array is null. The array is empty.
Inside the stored procedure, the parameter value is first tested for Null using the ISNULL function and then checked whether it is Blank (Empty). If the parameter has value then only matching records will be returned, while if the parameter is Null or Blank (Empty) then all records from the table will be returned.
The len() function is used to find the number of elements in the list. So, to check if the list is empty or not using len(), we can pass the empty list to the len() function, and if we get 0, that means the list is empty.
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 ...:
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?
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
