Re: [GENERAL] Postgres case insensitive searches

> Subject: Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches
> From: haramrae@gmail.com
> Date: Sat, 29 Jun 2013 09:37:51 +0200
> CC: laurenz.albe@wien.gv.at; pgsql-general@postgresql.org; pgadmin-support@postgresql.org
> To: udayabhanu1984@hotmail.com
>
> On Jun 29, 2013, at 3:59, bhanu udaya <udayabhanu1984@hotmail.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?
>

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

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