Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail From: Mladen Gogala Newsgroups: comp.databases.postgresql Subject: Re: Case-insensitive select Date: Thu, 1 Sep 2011 12:57:52 +0000 (UTC) Organization: solani.org Lines: 31 Message-ID: References: <25ee8bf2-869c-427b-a4a4-5faf86f92d57@o26g2000vbi.googlegroups.com> <1314860211.105064@proxy.dienste.wien.at> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Trace: solani.org 1314881872 25675 eJwFwQERADEIAzBLA9YCcvj18C/hEwSNLy/Bi8Uye+cldosZ7QrDqupEoe0UgYMYn0+QXD8LoBCI (1 Sep 2011 12:57:52 GMT) X-Complaints-To: abuse@news.solani.org NNTP-Posting-Date: Thu, 1 Sep 2011 12:57:52 +0000 (UTC) User-Agent: Pan/0.133 (House of Butterflies) X-User-ID: eJwFwYEBwCAIA7CXEGnFc1gH/59ggs1FnSAYGMzIP2PsrlPw/itjpbknfLqlNNYBTdTV9fUAII8Q+w== Cancel-Lock: sha1:APPWzza5UJIGY9Cjd5IBCqhPTvQ= X-NNTP-Posting-Host: eJwNy8kBwCAIBMCWQHY5ylHQ/ktI5j80V++A08HHt2bvIonDVGiEn5FeS+5KTlwdD1Z5VYuHpQr+Z5tAPVzo2dYl2SttkvgAjSYYKg== Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:225 On Thu, 01 Sep 2011 08:56:31 +0200, Laurenz Albe wrote: > Mladen Gogala wrote: >>> There's a case-insensitive string type in contrib (the "Additional >>> Supplied Modules"): > >> 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? > > Yours, > Laurenz Albe 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. " -- http://mgogala.byethost5.com