Re: Disabling case sensitivity

On Wed, Jul 10, 2002 at 03:22:00PM +0200,
 Michael Meskes <meskes@postgresql.org> wrote
 a message of 31 lines which said:

> Can't you use something like
>
> SELECT from table where tolower(username)='good';

If you want sometimes case-sensitive comparison and sometimes not, it
is a good solution (the PostgreSQL extension ILIKE is another). But if
you want to "disable case-sensitivity", you risk that some
applications forget the call to tolower(). Therefore, I prefer to
create a trigger which will force the field to lowercase before
INSERTing it. (The problem of my solution is that it is no longer
case-preserving.)

CREATE FUNCTION force_lower_case() RETURNS OPAQUE
   AS 'BEGIN
          NEW.name = lower(NEW.name);
          RETURN NEW;
       END;'
     LANGUAGE PLPGSQL;

-- Domain names are only in US-ASCII (so no locale problems) and are
-- case-insensitive. If you want to record the original case, add a
-- new field.
CREATE TRIGGER force_lower_case
   BEFORE INSERT ON Domains
   FOR EACH ROW
   EXECUTE PROCEDURE force_lower_case();