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