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 ?

22 Jul 2001 03:16:55 -0700
domingo@dad-it.com (Domingo Alvarez Duarte)
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

Mon, 23 Jul 2001 11:04:09 +0300
Alessio Bragadini <alessio@albourne.com>
Re: unique index doesn't accept functions on fields
> > 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


Tue, 24 Jul 2001 09:23:08 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Re: unique index doesn't accept functions on fields