RE: Simple Question: Case sensitivity

Simply create a functional index:

CREATE INDEX foo ON employees (UPPER(firstname));

However, I have just tried this, and it doesn't work for some reason.
Anybody?

Cheers...

MikeA

-----Original Message-----
From: Tomas Berndtsson [mailto:tomas(at)nocrew(dot)org]
Sent: 11 December 2000 10:49
To: Hancock, David (DHANCOCK)
Cc: 'pgsql-general(at)postgresql(dot)org '
Subject: Re: [GENERAL] Simple Question: Case sensitivity

"Hancock, David (DHANCOCK)" <DHANCOCK(at)arinc(dot)com> writes:

> Abe: It's an SQL thing or a scripting thing. It's probably easiest and
> safest in the SQL:
>
> select firstname, surname from employees
> where upper(firstname) like upper('%$criteria%') or
> upper(surname) like upper('%$criteria%')
>
> That is, force the column and the search string to uppercase befor
> comparing, and it won't matter how it's stored in the database.

Related to this, is there any way to make an index for a table
case-insensitive? If you have an index, but use upper() in the select,
the index is not used.

Tomas

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************