Re: Howto have a unique restraint on UPPER (textfield) - Mailing list pgsql-sql

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@josh# create table c (d text);
> CREATE TABLE
> 5432 josh@josh*# create unique index c_ix on c (upper(d));
> CREATE INDEX
> 5432 josh@josh*# insert into c (d) values ('text');
> INSERT 0 1
> 5432 josh@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.