Tuesday, May 12, 2009

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.

74 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#

Francisco Figueiredo Jr. said...

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!

Kansas City Kansas yellow pages said...

Nice, I think it could be interesting to add some more entries following this one, and probably it's not only me having this opinion. Cheers! Please come visit my site Kansas City Kansas yellow pages when you got time.

Hollywood Florida yellow pages said...

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.

poll 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.

poll said...

I really liked your blog! You have some great content. Check out my blog and give me some feedback Please come visit my site Minneapolis 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.

Business Directory San Diego said...

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.

Business Directory Dallas said...

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...

酒店打工

酒店兼職

台北酒店

打工兼差

酒店工作

酒店經紀

禮服店

酒店兼差

酒店上班

酒店PT

酒店

poll 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 Akron Yellow Page Business Directory when you got time.
Thanks.

poll 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.

leadership development institute said...

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.

life skills coach said...

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.

doll 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.

Jiangsu Pictures said...

I really liked your blog! I would love some feedback on my site Jiangsu China when you got time.

Kunming Pictures said...

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.

酒店上班請找艾葳 said...

艾葳酒店經紀公司提供專業的酒店經紀, 酒店上班小姐,八大行業,酒店兼職,傳播妹,或者想要到打工兼差打工,兼差,或者八大行業,酒店兼職,想去酒店上班, 日式酒店,制服酒店,ktv酒店,禮服店,整天穿得水水漂漂的,還是想去制服店上班小姐,水水們如果想要擁有打工工作、晚上兼差工作兼差打工假日兼職兼職工作酒店兼差兼差打工兼差日領工作晚上兼差工作酒店工作酒店上班酒店打工兼職兼差兼差工作酒店上班等,想了解酒店相關工作特種行業內容,想兼職工作日領假日兼職兼差打工、或晚班兼職想擁有快速賺錢又有保障的工作嗎???又可以現領請找專業又有保障的艾葳酒店經紀公司!

艾葳酒店經紀是合法的公司工作環境高雅時尚,無業績壓力,無脫秀無喝酒壓力,高層次會員制客源,工作輕鬆,可日領現領
一般的酒店經紀只會在水水們第一次上班和領薪水時出現而已,對水水們的上班安全一點保障都沒有!艾葳酒店經紀公司的水水們上班時全程媽咪作陪,不需擔心!只提供最優質的酒店上班,酒店上班,酒店打工環境、上班條件給水水們。心動嗎!? 趕快來填寫你的酒店上班履歷表

水水們妳有缺現領、有兼職缺錢卡奴的煩腦嗎?想到日本留學缺錢嗎?妳是傳播妹??想要擁有高時薪又輕鬆的夜間兼職工作,打工機會和,假日打工,假日兼職賺錢的機會嗎??想實現夢想卻又缺錢沒錢嗎!??
艾葳酒店台北酒店經紀招兵買馬!!徵專業的酒店打工,想要去酒店的水水,想要短期日領,酒店日領,禮服酒店,制服店,酒店經紀,ktv酒店,便服店,酒店工作,禮服店,酒店小姐,酒店經紀人,
等相關服務 幫您快速的實現您的夢想~!!

crystal said...

Do not mean bad.Thank you so much! I just want to show some fashion wedding dress to all of you. I like wedding dresses, because wedding for a girl, it is the most special day in her life. Do you want to have the beautiful wedding dresses in perfect day??

crystal said...

Fantastic! God bless you! Meanwhile, we have the highest quality but the lowest price fashion cheap wedding dresses. Here are the most popular designer wedding dresses and lace wedding dresses for all of you. Also the cheap evening dresses is a great choice for you. Let you dream come ture!!

crystal said...

Perfect!! You are a outstanding person! Do you want to wear stunning discount wedding dresses and join the party wearing beautiful cheap prom dresses?? Or you want all people's eyes can't move from you? The evening formal dresses are the best gift for you!

crystal said...

Best wishes for you! May be you are interested in party cocktail dresses, and you may need cheap bridesmaid dress for you special Occasions. We can provide cheap cocktail dresses, cheap flower girl dresses even the plus size wedding dresses. You can choose that you like!

crystal said...

http://luoluo123.blogspot.com
http://crystalluo612.blog126.fc2.com
http://lj83612.spaces.live.com
http://cheappolos.blog.drecom.jp
http://d.hatena.ne.jp/specialoccasiondress
http://crystal888.edublogs.org
http://www4.atword.jp/summerfashion

crystal said...

http://blog.oricon.co.jp/misspoloshirt
http://blog.qlep.com/blog.php/gillette
http://blog.goo.ne.jp/pumafr
http://www.pumafr.com/blog
http://www.clothessports.com
http://chinaclothes.net

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.

post traumatic stress disorder symptoms 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 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.

poll said...

I'm glad you're back to posting. I love reading about your shopping adventures and great finds. Your outfits are always super cute too! Very inspirational. Please come visit my site Durham Business Directory when you got time.

poll 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.

crazyloko said...

buy products wholesaleThis phenomenom is typified by China Wholesalersthe rise ofbusiness. Incredible range of products available with China Wholesale “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and China Wholesale will skyroket your profits.china wholesale productsbuy china wholesalewholesale chinawholesale productsbuy products

crazyloko said...

We offer designer , custom wedding dress, cheap cell phonescheap wedding dressesbridal gowns, wedding gowns, Flower Girl Dresses andwholesale cell phonesso on. Buy tailor-made anddesigner wedding dresses designer to be a special brides now!

crazyloko said...

Wholesale Gemstone cheap Gemstone Jewelry from China, discount wholesale jewelry Beads, wholesale Gemstone jewelry cheapest Wholesale Naruto anime and manga cheap cosplay costumes, cheap Naruto cosplay costumes on sale, buy Naruto cosplay naruto online shop.Wholesale anime and manga wholesale crystal jewelry costumes, cheap cosplay costumes on sale, buy anime cosplay costumes online shop

poll 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.

poll said...

right. lol! Thanks for the information you post. Please come visit my site El Paso Business Directory when you got time.

poll 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.

poll 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.

christian adoption said...

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.

rr8004 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.

rr8004 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.

rr8004 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.

rr8004 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 San Jose Directory when you got time.

yi said...

Hello, everybody. I am a new hand to be here. So nice to meet you all. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Luis said...

Thanks! for solving my query . You always help me in my problem.
cosmetic dentists edinburgh

rr8004 said...

You share valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!! Thumbs up. Please come visit my site Philadelphia Business Phone Book when you got time.

Eric 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.

Eric 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.

Luis said...
This comment has been removed by the author.
lilena said...

Online resources of toe fungus, fungus treatment, nail fungus toe. foot fungus

lilena said...

Potty training tips, baby potty training. Resource guide on how to potty train, potty training for boys and girls.how potty train

rr8004 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.

rr8004 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.

thomson said...

Online resources of toe fungus, fungus treatment, nail fungus toe. toe fungus

thomson said...

Potty training tips, baby potty training. Resource guide on how to potty train, potty training for boys and girls.Potty training tips

lady said...

KABUL, Afghanistan — Tainted by ed hardy mens clothing
a flawed election and ed hardy womens shoes
allegations of high-level corruption in his regime, ed hardy mens shoes
President Hamid Karzai ed hardy mens longsleeve
was inaugurated Thursday for a second term, ed hardy mens jackets
saying the Afghan Army should assume full control of the country’s security within five years. Ed Hardy chothing
“We will decrease the role of international forces,” ed hardy womens jackets
Mr. Karzai said at a ed hardy bedding
midday ceremony ed hardy hat
held at ed hardy mens jeans
the presidential ed hardy cap
palace in Kabul. ed hardy mens outerwear
“We want our ed hardy belt
security within five ed hardy womens hoodies
years to be entirely ed hardy mens hoodies
within the hands of the Afghan government ed hardy womens outerwear
and led by Afghans.”
The ceremony ed hardy jackets
was the culmination of a fraught ed hardy longsleeve
and chaotic electoral process that began on Aug. ed hardy cap
20 when Afghans went to the polls. ed hardy hat
Mr. Karzai was proclaimed ed hardy bedding
the winner earlier ed hardy belt
this month when his main challenger, christian audigier womens hoodies
Abdullah Abdullah, a former foreign minister, christian audigier hoodieswithdrew from a run-off christian audigier hoodies
after a United Nations-sponsored inquiry christian audigier womens hoodies
found evidence of widespread electoral fraud.

thomson said...

Growing Corn, corn plant, corn planting, sweet corn planting basics. Growing Corn

thomson said...

Gynecomastia treatment, gynecomastia causes, gynecomastia cure, male breast enlargement guide.gynecomastia

lilena said...

Gynecomastia treatment, gynecomastia causes, gynecomastia cure, male breast enlargement guide.gynecomastia causes

lilena said...

Herniated disc symptoms, Herniated Disc Pain, treatment and developmental online guide. herniated disc symptoms

Gigi said...

$75 Replica Rolex Watches sale, Our site provides Rolex replica, replica Rolex Watches, replica breitling, replica Cartier, replica Omega, Tag Heuer Watches. . .more than 100 famous rolex brands"
rolex air king
rolex datejust
rolex day date
rolex daytona
rolex gmt
rolex submariner
rolex yachtmaster
a lange sohne watches
alain silberstein watches
bell ross watches
breguet watches
breitling watches
bvlgari watches
cartier watches
omega watches
patek philippe watches
ロレックスレプリカ
ロレックスコピー

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.

Luis 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.

chaoyang said...

Your blog is wonderful, I like it very much, thank you!
By the way, do you like polo shirts, which are very chic, especially the polo t shirts, I love them very much. I also like playing tennis rackets, it can keep healthy, what do you like to do?
We are the outlet of polo t shirts women, polo t shirts on sale, polo t shirts for women, polo shirts on sale, these products are best-seller in our store online.besides we also sell polo shirts men, men's polo shirt, men polo shirt, mens polo shirts, mens polo shirt and cheap polo shirts, discount polo shirts, men's polo shirts, women's polo shirts We are also the outlet of cheap tennis racket, discount tennis racket and the main product is prince tennis racquet, head tennis rackets, wilson tennis racket, babolat tennis racquet. So if you love sports, you should not miss our store, we can meet what you want, and you can find many surprise in our store

rr8004 said...

I was thinking of looking up some of them newspaper websites, but am glad I came here instead. Although glad is not quite the right word… let me just say I needed this after the incessant chatter in the media, and am grateful to you for articulating something many of us are feeling - even from distant shores. Please come visit my site Philadelphia Business Services And Classifieds when you got time.

rr8004 said...

I was thinking of looking up some of them newspaper websites, but am glad I came here instead. Although glad is not quite the right word… let me just say I needed this after the incessant chatter in the media, and am grateful to you for articulating something many of us are feeling - even from distant shores. Please come visit my site Pennsylvania PA Phone Directory when you got time.