Thread: ignore case in where clause
I am currently using lower(column) = '' for matching case insensitive. I know that there are ways to do this with regular expressions too. I recently noticed that including even one lower causes severe performance issues (from 290ms to over 80Kms). What is the best way, performance wise, to do case insensitive matching? I could spend a few hours testing if I have to, but I'm hoping someone knows off the top of their heads. Using 8.3 currently. I am a programmer, not a database person; but we don't really have a DB here, so I do what I can. Thanks. Edward W. Rouse Comsquared System, Inc. 770-734-5301
On Thu, 2012-03-22 at 16:26 -0400, Edward W. Rouse wrote: > I am currently using lower(column) = '' for matching case insensitive. I > know that there are ways to do this with regular expressions too. I recently > noticed that including even one lower causes severe performance issues (from > 290ms to over 80Kms). > Probably because it cannot use the index anymore. Try creating an index on lower(column), and see if it helps. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
That was exactly it, Thanks. > -----Original Message----- > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql- > owner@postgresql.org] On Behalf Of Guillaume Lelarge > Sent: Thursday, March 22, 2012 4:39 PM > To: Edward W. Rouse > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] ignore case in where clause > > On Thu, 2012-03-22 at 16:26 -0400, Edward W. Rouse wrote: > > I am currently using lower(column) = '' for matching case > insensitive. I > > know that there are ways to do this with regular expressions too. I > recently > > noticed that including even one lower causes severe performance > issues (from > > 290ms to over 80Kms). > > > > Probably because it cannot use the index anymore. Try creating an index > on lower(column), and see if it helps. > > > -- > Guillaume > http://blog.guillaume.lelarge.info > http://www.dalibo.com > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql