Re: Case insensitive primary keys

Jeff Schnitzer wrote:
>
> Hello!  I've got a question:
>
> I'm trying to create a system with a string (varchar) primary key which
> is *not* case sensitive.  Is there any way I can create a primary key
> column which automatically takes care of this?

you can try something like:

CREATE UNIQUE INDEX foo_idx ON tbl (lower(foo));

That will prevent entries duplicates that differ only in case.

> I'm coming at this problem from a java app server, so my hands are
> somewhat bound regarding the queries used to insert and search the
> table.  There are solutions, but the ideal (for me) would be for the
> database to ignore the case of my key column.  Is this possible?

If you can't change LIKE to ILIKE or foo='x' to lower(foo) = lower('x')
you'll have problems with comparisons.

The only other thing(s) I can suggest are:

1. add a rule/trigger to inserts/updates/selects to do the lower() stuff
for you.
2. you _could_ write your own type, but this is last resort really.

- Richard Huxton