PostgreSQL Discussion - Unique constraint or index, case insensitive, on multiple fields

Author

Unique constraint or index, case insensitive, on multiple fields

MargaretGillon@chromalloy.com

2006-04-06, 1:29 pm

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

Tom Lane

2006-04-06, 8:25 pm

MargaretGillon@chrom
alloy.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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links