Postgres Pro
Downloads
Home   >   mailing lists

Thread: Unique constraint or index, case insensitive, on multiple fields

Unique constraint or index, case insensitive, on multiple fields

From
MargaretGillon@chromalloy.com
Date:

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

Re: Unique constraint or index, case insensitive, on multiple fields

From
Tom Lane
Date:
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

Re: Unique constraint or index, case insensitive, on multiple

From
MargaretGillon@chromalloy.com
Date:

>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