Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #221

Re: Case-insensitive select

Show all headers | View raw

On Wed, 31 Aug 2011 05:29:13 -0700, RVince wrote:

> I'm using Postgres and the end-users are complaining that the search
> features provided by the web app are case-sensitive (via Postgres). Is
> there a way to do a case-insensitive version of select statements? Not
> all statements are of the form "=" or "like" but often of the form
> "...where lastname >= 'smith';" so the I don;t think the '~*' operator
> will work here for me in all cases. Is there a way to do this type of
> case-insensitive comparison? Is there a way to designate certain tables
> or columns be compared on a case-insensitive basis ? Thanks RVince

First of all, PostgreSQL supports ILIKE operator, which is case 
insensitive. Second, ~* will have a problem with indexes, which limits 
its usefulness. Third, PostgreSQL support standard function like "upper" 
and "lower". Your comparison can be rewritten like 
where lower(lastname) >= 'smith', which will be case insensitive. 
PostgreSQL also allows you to create an index on lower(lastname), which 
will probably be used, unless the number of Smiths in your name 
collection isn't too big for that.



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