Re: Case Insensitive Data Type

I found this method in the archives:

CREATE TABLE t1 (f1 varchar(40));
SELECT f1 FROM t1 WHERE lower( f1 ) = 'whatever';
CREATE INDEX t1_f1_idx ON t1 (lower(f1));

This will unfortunately not work, since I'm using CMP EJB's, and I'm not writing the SQL queries myself. The EJB container does it for me, and it wants to do a
SELECT f1 FROM t1 WHERE f1 = 'whatever';
Instead of
SELECT f1 FROM t1 WHERE lower( f1 ) = 'whatever';

This is why I'm hoping to have the database backend take care of this with perhaps a new datatype, so that the '=' operator is case insensitive.

Russell
----- Original Message -----
From: Russell Black
To: pgsql-general(at)postgresql(dot)org
Sent: Friday, May 24, 2002 10:46 AM
Subject: [GENERAL] Case Insensitive Data Type

I'm using email addresses as a primary key in one of my tables. Currently, I have to ensure that the email addresses are converted to lower case before they get put into the table, and that all lookups on that field are converted to lower case before the select statement, in order to ensure that Joe(at)Somewhere(dot)com is the same as joe(at)somewhere(dot)com(dot)

Does anyone know of a case-insensitive data type? I'd want the following behavior:

/* Make the primary key be a case-insensitive data type */
CREATE TABLE foo (email CASE_INSENSITIVE_VARCHAR(50) PRIMARY KEY, name VARCHAR(50));

/* Insert a row with a case insensitive key */
INSERT INTO foo VALUES ('joe(at)somewhere(dot)com', 'Joe');
INSERT 24751 1

/* A different case of an existing primary key should fail */
INSERT INTO foo VALUES ('Joe(at)SOMEWHERE(dot)com', 'Joe');
ERROR: Cannot insert a duplicate key into unique index foo_pkey

/* A lookup on a different case of an existing key should be successful: */
SELECT * FROM foo WHERE email = 'Joe(at)SOMEWHERE(dot)com';
email | name
-------------------+------
joe(at)somewhere(dot)com | Joe
(1 row)

Anyone know how I can accomplish this? Can I create a custom data type to do this?

Thanks, Russell