Re: Case Insensitive Data Type

Thanks for your quick reply!

The first option you gave looks promising. Unfortunately, I have no control
over the insert or select statements, since I'm using EJBs with Container
Managed Persistence. The EJB container does the INSERT and UPDATE SQL.
This means options two and three in your suggestions wouldn't work. But it
looks like your first option would ensure that the data is lower case in the
table, which gets me half way there. Now I just need a way to make the =
operator for the email column case-insensitive. So that I could do

INSERT INTO foo VALUES ('abcde');

and selecting on an upper case version would work:

SELECT * FROM foo WHERE (email = 'ABCDE');

Any suggestions?

Thanks

----- Original Message -----

> Three ways:
>
> One: write trigger on the table.
>
> CREATE OR REPLACE FUNCTION foo_trigger_func() RETURNS OPAQUE AS '
> BEGIN
> NEW.email := LOWER(NEW.email);
> RETURN NEW;
> END;' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER foo_trigger BEFORE INSERT OR UPDATE ON foo
> FOR EACH ROW EXECUTE PROCEDURE foo_trigger_func();
>
> Two: use the lower function in your update and insert queries
>
> INSERT INTO foo VALUES(LOWER(email),'Name');
>
> Three: Postgresql has an ILIKE operator
>
> You could play with that but not the best solution
>
> HTH
>
> Darren Ferguson
>
> On Fri, 24 May 2002, Russell Black wrote:
>
> > 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
> >
> >
> >
>