Thread: case insensitive database ?

Hi
Is it possible to setup a database that is case insensitive ?
is it also possible with a multbyte database ?

Thanx Thorsten

Thorsten Mauch wrote:
>
> Hi
> Is it possible to setup a database that is case insensitive ?
I don't think you can compile with an case-insensitive flag - AFAIK. But
the workaround is to user the upper() function: SELECT * FROM MyTable
where upper(MyField) = 'UpperCaseString';

In your application that you connect to Postgres, you convert your
string to an upper case string.

> is it also possible with a multbyte database ?
It shouldn't make any difference.

Poul L. Christiansen

On Wed, 25 Apr 2001, Thorsten Mauch wrote:

> Is it possible to setup a database that is case insensitive ?
> is it also possible with a multbyte database ?

AFAIK, no, it's not possible to set up a case-insensitive database.
We're very into sensitivity in this community. :-)


Some possibilities:

the ILIKE operator does case insensitive searching,
as does the case insensitive regex operator (~*).


search using lower() or upper().
select * from pers where lower(fname) = 'joel';

(to speed these up, you can index pers on lower(fname)!)
"\h create index" for help


have two fields, fname and fname_lower, with a trigger to copy
lower(fname) to fname_lower on update.


Good luck!

HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

On Wed, 25 Apr 2001, Leandro Fanzone wrote:

> # select * from table where lower(field) ilike '%something%';
> ERROR:  parser: parse error at or near "ilike"

ILIKE is new in 7.1. With ILIKE, you don't have to use lower() -- the
point of ILIKE is to perform the comparison case-insensitively:

  SELECT * FROM table WHERE field ILIKE 'foobar';

> Another question: if I have an index on lower(field), does it make the like
> search faster? Excuse me if this question is too ignorant.

Not ignorant at all.

As long as PostgreSQL uses the index, it *should* be a good
performance boost. The subject of when PG decides to use an index, and
whether that always is a speed increase can be complicated -- the
Developer's manual covers some of the fine points.

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington