Thread: Case insensitive primary keys

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?

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?

Thanks,
Jeff

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