Thread: unique index on more than one field using functions

I'm trying create a unique index using more than one field and
applying a function in one field to achieve case insensitive
uniqueness but postgresql doesn't accept.



create table a( 
 id int primary key, id2 int not null, name varchar(50), unique(id2, lower(name)) );

Anyone have an idea ?

On 22 Jul 2001, Domingo Alvarez Duarte wrote:

> I'm trying create a unique index using more than one field and
> applying a function in one field to achieve case insensitive
> uniqueness but postgresql doesn't accept. 
> 
> create table a( 
> 
>   id int primary key,
>   id2 int not null,
>   name varchar(50),
>   unique(id2, lower(name))
>   );
> 
> Anyone have an idea ?

IIRC, Functional indexes are constrained to a single function
with one or more column references (no constants, etc), so
you can't precisely do the above directly.  You might be
able to make a function which takes id2 and name and combines
them in some way returning a single varchar and make the unique
index on that result.