Обсуждение: Unique constraint or index, case insensitive, on multiple fields

Поиск
Список
Период
Сортировка

Unique constraint or index, case insensitive, on multiple fields

От
MargaretGillon@chromalloy.com
Дата:

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

От
Tom Lane
Дата:
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

От
MargaretGillon@chromalloy.com
Дата:

>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