Re: [GENERAL] Postgres case insensitive searches

> Subject: Re: [pgadmin-support] [GENERAL] Postgres case
insensitive searches

> From: haramrae(at)gmail(dot)com

> Date: Sat, 29 Jun 2013 09:37:51 +0200

> CC: laurenz(dot)albe(at)wien(dot)gv(dot)at;
pgsql-general(at)postgresql(dot)org; pgadmin-support(at)postgresql(dot)org

> To: udayabhanu1984(at)hotmail(dot)com

>

> On Jun 29, 2013, at 3:59, bhanu udaya
<udayabhanu1984(at)hotmail(dot)com> wrote:

>

> > Thanks. But, I do not want to convert into upper and
show the result.

>

> Why not? It won't modify your results, just the search
condition:

>

> SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER
BY val;

>

> Or:

>

> SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER
BY upper(val), val;

>

>

> > Example, if I have records as below:

> > id type

> > 1. abcd

> > 2. Abcdef

> > 3. ABcdefg

> > 4. aaadf

> >

> > The below query should report all the above

> >

> > select * from table where type like 'ab%'. It should
get all above 3 records. Is there a way the database itself
can be made case-insensitive with UTF8 characterset. I tried
with character type & collation POSIX, but it did not
really help.

>

> I was under the impression this would work, but ISTR that
not every OS has this capability (Postgres makes use of the OS
collation mechanics). So, what OS are you running the server
on?

>

Duplicate the column with an upper or lowercase version and run all
queries against that.

CREATE TABLE foo (

id serial PRIMARY KEY,

val text,

val_lower text

);

Index val_lower. Use triggers to keep val and val_lower in sync and
discard all attempts to write directly to val_lower. Then all
queries would be of the form

SELECT id, val

FROM foo

WHERE val_lower LIKE 'ab%';

Wouldn't want to write every table like this, but if (a) query speed
trumps all other requirements and (b) functional index, CITEXT, etc.
have all been rejected as not fast enough…

--Lee

--
Lee Hachadoorian
Assistant Professor in Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu
It is a good idea to have a duplicate column and index and use that column. But, we have heavyinserts/updates on this table. I am afraid that it would slow down the insert performance. But, I would definately like to test this option. Isn't it better to convert Postgres DB to case insensitive ?How difficult is that ? I want the DB to support UTF8 and be case insensitive like SQL Server. Thanks