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.
Labels: mono, Npgsql, postgresql
