Re: Howto have a unique restraint on UPPER (textfield)

Joshua Tolley schrieb:
> On Sun, Jan 31, 2010 at 03:26:14AM +0100, Andreas wrote:
>
>> Hi,
>>
>> is there a way to define a unique restraint on UPPER (textfield)?
>>
>> E.g. mytable (
>> name_id serial PRIMARY KEY,
>> name varchar(255),
>> UNIQUE ( upper (name) )
>> )
>>
>> psql throws a syntax error because of the upper() function.
>>
>> I need to prohibit that 2 of strings like cow, Cow, CoW appears in
>> the name-column.
>>
>
> Like this:
>
> 5432 josh(at)josh# create table c (d text);
> CREATE TABLE
> 5432 josh(at)josh*# create unique index c_ix on c (upper(d));
> CREATE INDEX
> 5432 josh(at)josh*# insert into c (d) values ('text');
> INSERT 0 1
> 5432 josh(at)josh*# insert into c (d) values ('tExt');
> ERROR: duplicate key value violates unique constraint "c_ix"
>
Thanks for clearing this up. :)

It works with CREATE UNIQUE INDEX.
So I had the missconception that UNIQUE (...) within CREATE TABLE (...)
was actually just an shorter way to define a unique index which it is not.