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

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.

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"

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Hi,

I think you need CS collation and UNIQUE(name).

> 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. 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) 
> To make changes to your subscription: 
> http://www.postgresql.org/mailpref/pgsql-sql 
> 

Здесь спама нет http://mail.yandex.ru/nospam/sign

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.

Andreas <maps.on@gmx.net> writes:
> 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.

Well, it is that --- it just doesn't provide access to all the features
that CREATE INDEX does.
        regards, tom lane

Tom Lane schrieb:
> Andreas <maps.on@gmx.net> writes:
>   
>> 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.
>>     
>
> Well, it is that --- it just doesn't provide access to all the features
> that CREATE INDEX does.
>   
So as it is a shortcut for "create index" then why would the function 
call of upper not be accepted when the sql parser maps the 
uniqe-constraint into the "create index" command? The parser could just 
take everything in the ( ) and use it as is.

Somehow there must be a notice in the meta data to mark the difference.
pgAdmin shows a unique as constraint but no index when created within 
"create table".
The unique-index only shows up when created seperately.


regards
Andreas

Andreas <maps.on@gmx.net> writes:
> Tom Lane schrieb:
>> Well, it is that --- it just doesn't provide access to all the features
>> that CREATE INDEX does.
>> 
> So as it is a shortcut for "create index" then why would the function 
> call of upper not be accepted when the sql parser maps the 
> uniqe-constraint into the "create index" command?

Because the UNIQUE constraint syntax is defined by the SQL standard,
and among other things the standard requires all UNIQUE constraints
to be represented in the information_schema.  But the information_schema
views don't have the flexibility to represent anything but simple column
values in a unique constraint.  So we just expose that in CREATE INDEX,
which is outside the standard anyway.
        regards, tom lane

Andreas wrote:
> is there a way to define a unique restraint on UPPER (textfield)?
> psql throws a syntax error because of the upper() function.
The third section of the create index command at 
http://www.postgresql.org/docs/8.4/interactive/sql-createindex.html 
describes a function based index with as example the function .... 
upper! :-)

postgres=# create table aap (a text);
CREATE TABLE
postgres=# create unique index ai on aap (upper(a));
CREATE INDEX
postgres=# insert into aap values ('aap');
INSERT 0 1
postgres=# insert into aap values ('aaP');
ERROR:  duplicate key value violates unique constraint "ai"

regards,
Yeb Havinga