Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!newsfeed.utanet.at!newscore.univie.ac.at!aconews-feed.univie.ac.at!aconews.univie.ac.at!not-for-mail From: "Laurenz Albe" Newsgroups: comp.databases.postgresql References: <25ee8bf2-869c-427b-a4a4-5faf86f92d57@o26g2000vbi.googlegroups.com> <1314860211.105064@proxy.dienste.wien.at> Subject: Re: Case-insensitive select Date: Thu, 1 Sep 2011 16:40:58 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.5931 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 Organization: MagistratWien newsserver Message-Id: <1314888079.999065@proxy.dienste.wien.at> X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/) X-Cache: nntpcache 2.3.3 (see http://www.nntpcache.org/) Lines: 28 NNTP-Posting-Host: 141.203.254.23 X-Trace: 1314888084 aconews.univie.ac.at 11354 141.203.254.23 X-Complaints-To: abuse@univie.ac.at Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:226 Mladen Gogala wrote: [about citext contrib] >>> There are unpleasant surprises with that, like the fact that unique >>> index of that type is not case insensitive. Standard varchar data type, >>> combined with a function index or a trigger should be more than >>> adequate. >> What you say seems to contradict citext's documentation: >> >> The citext data type allows you to eliminate calls to lower in SQL >> queries, and allows a primary key to be case-insensitive. >> >> Can you explain? > You skipped a passage in the documentation: > > "If you declare a column as UNIQUE or PRIMARY KEY, the implicitly > generated index is case-sensitive. So it's useless for case-insensitive > searches, and it won't enforce uniqueness case-insensitively. " Oh, I see. I think you got confused here, because the sentence you quote is a description of a drawback of the "standard approach" using "WHERE lower(col) = LOWER(?)", *not* a drawback of citext. Yours, Laurenz Albe