Unique constraint or index, case insensitive, on multiple fields

From: MargaretGillon(at)chromalloy(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Unique constraint or index, case insensitive, on multiple fields
Date: 2006-04-06 18:02:38
Message-ID: OF9DA2587D.BEB1D4A7-ON88257148.0061D884-88257148.00631CD8@CHROMALLOY.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guy Fraser 2006-04-06 18:12:57 Re: Backup method
Previous Message Tobias Herp 2006-04-06 17:30:23 encode/decode trouble ('invalid input syntax for type bytea'); bug?