Unique constraint or index, case insensitive, on multiple fields
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
MargaretGillon@chromalloy.com writes:
CREATE UNIQUE INDEX resource_refullname
ON resource USING btree (redtid, (upper(refullname) text_ops));
You need something newer than PG 7.3 to do that. 7.3 can't handle
functional indexes with more than one column. There are many other good
reasons to upgrade anyway.
BTW the correct syntax would be
CREATE UNIQUE INDEX resource_refullname
ON resource USING btree (redtid, (upper(refullname)) text_ops);
If you're going to put an opclass name, it goes outside the parens.
(The only reason the parens are required at all is to separate the
expression from the opclass name ...)
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote on 04/06/2006 11:33:57 AM:
MargaretGillon@chromalloy.com writes:CREATE UNIQUE INDEX resource_refullname
ON resource USING btree (redtid, (upper(refullname) text_ops));You need something newer than PG 7.3 to do that. 7.3 can't handle
functional indexes with more than one column. There are many other good
reasons to upgrade anyway.BTW the correct syntax would be
CREATE UNIQUE INDEX resource_refullname
ON resource USING btree (redtid, (upper(refullname)) text_ops);If you're going to put an opclass name, it goes outside the parens.
(The only reason the parens are required at all is to separate the
expression from the opclass name ...)regards, tom lane
I thought it might be version related. I had planned to upgrade soon and
this is another good motivator.
Thank you,
Margaret Gillon