Обсуждение: Re: unique index doesn't accept functions on fields

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))
>   );

Have you tried to just CREATE TABLE and later CREATE INDEX UNIQUE
USING... ?

-- 
Alessio F. Bragadini        alessio@albourne.com
APL Financial Services        http://village.albourne.com
Nicosia, Cyprus             phone: +357-2-755750

"It is more complicated than you think"    -- The Eighth Networking Truth from RFC 1925

> > 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))
> >   );
> 
> Have you tried to just CREATE TABLE and later CREATE INDEX UNIQUE
> USING... ?

Postgres does not support functional indexing on multi-key indices.

Chris