[GENERAL] Unique constraint or index, case insensitive, on multiple fields

[MargaretGillon]

I am on version 7.3. I have been able to build a case insensitive index to keep the refullname column unique with the following:

CREATE UNIQUE INDEX  resource_refullname
 ON resource  USING btree   (upper(refullname) text_ops);

However I have a table where I want to allow  a duplicate refullname if the redtid field (int4) is different. When I try building an index using the command below I get an error. I still need the refullname to be case insensitive.

CREATE UNIQUE INDEX resource_refullname
 ON resource  USING btree  (redtid, (upper(refullname) text_ops));

The index will work with  (redtid, refullname) but then the index is not case insensitive on refullname.

Thanks,
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297