Skip to main content

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.

Comments

Michael I. Buen said…
Postgresql and your Npgsql totally rocks! If I'm not mistaken this is the first of its kind, with other RDBMS I have to concatenate the array to string
Lafriks said…
Michael, in Oracle RDBMS you can do:
select * from field in the(collection);
but postgre rocks anyway :) Loving it :)
Michael I. Buen said…
oops! I knew that other rdbms can do that ;)

what I mean is I can't seem to recall if other rdbms have array facility. there ain't seems no way on other rdbms to allow passing of variable list in a straightforward manner. it's only Postgres I can think that has that sort of neatness. and with the newer Npgsql support for arrays, app development becomes way, way much better.


see other rdbms's kludge solutions:

http://forums.whirlpool.net.au/forum-replies-archive.cfm/928435.html

http://www.sqlmag.com/Article/ArticleID/44939/sql_server_44939.html

http://knol.google.com/k/franck-pachot/oracle-passing-a-list-as-bind-variable/17uabcrki6uux/2#
Thank you guys for let us know we are doing a good job with Npgsql! :)

And thank you for your support and feedback. You rock!
Thank you for your good humor and for allowing yourself to be convinced that this was the right show for you to work on. Please come visit my site Hollywood Florida yellow pages when you got time.
Anonymous said…
Since I’m new to blogging, these articles are greatly appreciated; very useful and informative blog and every body must visit this blog. Please come visit my site Tulsa Business Directory when you got time.
Towing said…
There is obviously a lot more than this. Would you mind telling me how long it took you to gather your content? Please come visit my site Towing Service when you got time.
Roofing Supply said…
This article was extremely interesting, especially since I was searching for thoughts on this subject last Thursday. Please visit my site Roofing Contractor when you got time.
sorry to ask this here but… I really love your theme, would it happen to be a free one i can download somewhere, or is it a custom theme you had made? Soon i will be launching my own blog, however i’m not great with designs but i do like the style of your site so it would be excellent if i could find (or buy) something with a similar look as my last designer cannot finish my site. Thanks! Please come visit my site San Diego Business Directory when you got time.
Congratulations, you just earned yourself an entry in my feed reader, great blog. Please come visit my site Dallas Yellow Page Business Directory when you got time.
Anonymous said…
You got a really useful blog I have been here reading for about an hour. I am a newbee and your success is very much an inspiration for me. Please come visit my site Virginia Beach Business Directory when you got time.
Thanks.
I enjoyed reading your work! GREAT post! I looked around for this… but I found you! Anyway, would you mind if I threw up a backlink from my site?
Please come visit my site leadership styles
when you got time. Thanks.
I usually don’t leave comments!!! Trust me! But I liked your blog…especially this post! Would you mind terribly if I put up a backlink from my site to your site? I would love some feedback on my site professional coaching
when you got time. Thanks.
rr8004 said…
I really liked your post on home financing! if you have more information elsewhere let me know. Please come visit my site Quebec facts when you got time.
I really liked your blog! I would love some feedback on my site Jiangsu China when you got time.
Can you provide more information on this? I am new to the subject. Feel free to check out my site Kunming China when you got time.
lupus symptoms said…
Wow! Thank you! I always wanted to write in my site something like that. Can I take part of your post to my blog?
Please come visit my site symptoms of lupus when you got time.
Thanks.
You got a really useful blog I have been here reading for about an hour. I am a newbee and your success is very much an inspiration for me. Please visit my site post traumatic stress disorder when you got time.
Thanks.
rr8004 said…
I really liked your post on home financing! if you have more information elsewhere let me know. Please come visit my site Oklahoma City Business Directory when you got time.
rr8004 said…
Yeah it is a great and nice article looking forward to have such article it is so useful. Please come visit my site Portland Yellow Page Business Directory when you got time.
rr8004 said…
You may have not intended to do so, but I think you have managed to express the state of mind that a lot of people are in. The sense of wanting to help, but not knowing how or where, is something a lot of us are going through. Please come visit my site Dallas Business Directory when you got time.
rr8004 said…
I am not really sure if best practices have emerged around things like that, but I am sure that your great job is clearly identifed. I was wondering if you offer any subscription to your RSS feeds as I would be very interested and can’t find any link to subscribe here. Please come visit my site San Jose Business Directory when you got time.
Anonymous said…
i love the style, i have several similar one, i usually wear them to work or shopping. Please come visit my site Local Business Directory Of Boston U.S.A. when you got time.
Anonymous said…
You do have a point here :) I admire the stuff you post and the quality information you offer in your blog! Keep up the good work dude. Please come visit my site Fortworth Yellow Page Business Directory when you got time.
Anonymous said…
right. lol! Thanks for the information you post. Please come visit my site El Paso Business Directory when you got time.
Anonymous said…
I really liked your blog! You have some great content. Please come visit my site Business Directory Listings Of Oklahoma when you got time.
Anonymous said…
I really liked your blog! You have some great content. Please come visit my site Business Directory Listings Of Oregon when you got time.
Hello, What enticed you to post this article. It was extremely interesting, especially since I was searching for info on this subject last Thursday. Please come visit my site adoptive parents when you got time.
shrubs said…
I really liked your post on home financing! if you have more information elsewhere let me know. Please come visit my site tree when you got time.
Anonymous said…
Wow! Thank you! I always wanted to write in my site something like that. Can I take part of your post to my blog? Please come visit my site Local Business Directory Of Boston U.S.A. when you got time.
Anonymous said…
Wow! Thank you! I always wanted to write in my site something like that. Can I take part of your post to my blog? Please come visit my site Boston City Directory when you got time.
Anonymous said…
This is just another reason why I like your website. I like your style of writing you tell your stories without out sending us to 5 other sites to complete the story. Please come visit my site Business Reviews Of San Jose City when you got time.
Unknown said…
Thanks! for solving my query . You always help me in my problem.
cosmetic dentists edinburgh
Unknown said…
Yeah it is a great and nice article looking forward to have such article it is so useful. Please come visit my site Manitoba, business directory give me any valuable feedbacks.
Unknown said…
It is very interesting article and quite impressive and more informative and looking forward to read such article. Please come visit my site cheap Wholesale Canada Watches when you got time.
Unknown said…
This comment has been removed by the author.
Anonymous said…
I found your blog on google and read a few of your other posts. I just added you to my Google News Reader. Keep up the good work. Look forward to reading more from you in the future. Please come visit my site Milwaukee Business Search Engine when you got time.
Anonymous said…
I found your blog on google and read a few of your other posts. I just added you to my Google News Reader. Keep up the good work. Look forward to reading more from you in the future. Please come visit my site Milwaukee Web Link when you got time.
rr8004 said…
I really liked your post on home financing! if you have more information elsewhere let me know. Please come visit my site Directory Fortworth City when you got time.
rr8004 said…
I really liked your post on home financing! if you have more information elsewhere let me know. Please come visit my site Fortworth Business Services And Classifieds when you got time.
Unknown said…
Thanks for the tips! Keep positing such nice tips.

undermount sinks
rr8004 said…
She’s really interested in this subject. Thanks again… Please come visit my site Arlington Business Services And Classifieds when you got time.
rr8004 said…
She’s really interested in this subject. Thanks again… Please come visit my site Texas TX Phone Directory when you got time.
john said…
it’s still important to familiarize yourself with ailments common to sliders so that you can quickly identify them in your own slider, should it become ill. The following are some of the most common medical problems among red-eared sliders.Emuaid Reviews
Download 2018 said…
I was about to say something on this topic. But now i can see that everything on this topic is very amazing and mind blowing, so i have nothing to say here. I am just going through all the topics and being appreciated. Thanks for sharing.

Popular posts from this blog

UUID datatype and COPY IN/OUT support added to cvs

Hi all! It was just added support to uuid datatype in cvs head. This type will be available in next Postgresql release 8.3. Thanks to David Bachmann for his patch! You can get more info about this patch in this mailing list post . Also was added support for copy in and copy out operations. Now, users can provide streams which can be copied directly to and from Postgresql tables! Thanks to Kalle Hallivuori for providing a patch! Thanks to Truviso for giving support to Kalle. More info about that including a demo and ready to use compiled Npgsql.dll versions can be found here . That's it! As soon as we get more features added, I will post info about them here. Stay tuned! :)

Stream seek error

Hi all! Since Npgsql RC1, we started to receive some error reports about problems when closing connections. The typical stack trace looked like this: System.NotSupportedException : This stream does not support seek operations. at System.Net.Sockets.NetworkStream.Seek(Int64 offset, SeekOrigin origin) at System.IO.BufferedStream.FlushRead() at System.IO.BufferedStream.WriteByte(Byte value) − at Npgsql.NpgsqlQuery.WriteToStream(Stream outputStream) in C:\Npgsql\Npgsql2\src\Npgsql\NpgsqlQuery.cs:line 62 − at Npgsql.NpgsqlReadyState.QueryEnum(NpgsqlConnector context, NpgsqlCommand command) in C:\Npgsql\Npgsql2\src\Npgsql\NpgsqlReadyState.cs:line 64 − at Npgsql.NpgsqlConnector.ReleasePlansPortals() in C:\Npgsql\Npgsql2\src\Npgsql\NpgsqlConnector.cs:line 373 − at Npgsql.NpgsqlConnectorPool.UngetPooledConnector(NpgsqlConnection Connection, NpgsqlConnector Connector) in C:\Npgsql\Npgsql2\src\Npgsql\NpgsqlConnectorPool.cs:line 541 − at Npgsql.NpgsqlConnectorPool.ReleasePooledConnector(NpgsqlConn