Npgsql Tips: Using " in (...)" queries with parameters list and "any" operator

Hi, all!

We have received some users questions about how to send a list of values to be used in queries using the "in" operator. Something like:

select foo, bar from table where foo in (blah1, blah2, blah3);

Npgsql supports array-like parameter values and the first idea to have this working would try to use it directly:

NpgsqlCommand command = new NpgsqlCommand("select * from tablee where field_serial in (:parameterlist)", conn);

ArrayList l = new ArrayList();
l.Add(5);
l.Add(6);

command.Parameters.Add(new NpgsqlParameter("parameterlist", NpgsqlDbType.Array | NpgsqlDbType.Integer));

command.Parameters[0].Value = l.ToArray();


NpgsqlDataReader dr = command.ExecuteReader();


but unfortunately this won't work as expected. Npgsql will send a query like this:

select * from tablee where field_serial in ((array[5,6])::int4[])

And Postgresql will complain with the following message:

ERROR: operator does not exist: integer = integer[]

This is because it is trying to compare the integer value of the column with an array of integers.

But, thanks to Josh Cooley and Jerónimo Milea who posted a solution on our forum, you just have to change your "in" operator to use the "any" operator and it will work as expected:

NpgsqlCommand command = new NpgsqlCommand("select * from tablee where field_serial = any (:parameterlist)", conn);

So if you want to use a query to compare a list of values, remember to use the any operator.