Groups > comp.databases.postgresql > #225

Re: Case-insensitive select

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 <gogala.mladen@gmail.com>
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	<pan.2011.09.01.12.57.52@gmail.com> (permalink)
References	<25ee8bf2-869c-427b-a4a4-5faf86f92d57@o26g2000vbi.googlegroups.com> <WS*So6Lt@news.chiark.greenend.org.uk> <pan.2011.08.31.23.00.27@gmail.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

Show key headers only | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Case-insensitive select RVince <rvince99@gmail.com> - 2011-08-31 05:29 -0700
  Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-31 13:19 +0000
  Re: Case-insensitive select Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-08-31 20:42 +0100
    Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-31 23:00 +0000
      Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-01 08:56 +0200
        Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-01 12:57 +0000
          Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-01 16:40 +0200
            Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-01 21:26 +0000
              Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-02 10:08 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-02 13:03 +0000
                Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-05 09:45 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-05 10:53 +0000
                Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-05 16:13 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-06 02:21 +0000
              Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-02 10:08 +0200

csiph-web