Re: Use of !~* to keep a varchar column UNIQUE

On 8/18/06 12:38 PM, "Don Morrison" <donmorrison(at)gmail(dot)com> wrote:

> Hello All,
>
> Is there a sane way to do this?
>
> I have a table with a column: "name VARCHAR(40) NOT NULL UNIQUE"
>
> Two problems with this:
>
> 1)UNIQUE is case-sensitive
> 2)UNIQUE is whitespace sensitive
>
> Has anyone come up with a way, or ways to trim leading and trailing
> whitespace, then do a case-insensitive uniqueness check efficiently
> and semi-elegantly?

You can define your index to be unique on a function of the column. As an
example:

create table testtable (
name varchar(40) not NULL
);
create unique index my_case_insensitive_index on testtable(lower(name));

insert into testtable(name) values ('John');
INSERT 0 1
insert into testtable(name) values ('john');
ERROR: duplicate key violates unique constraint "my_case_insensitive_index"

You get the idea, hopefully.

Sean