Postgres Pro
Downloads
Home   >   mailing lists

Unique constraint or index, case insensitive, on multiple fields - Mailing list pgsql-general

From MargaretGillon@chromalloy.com
Subject Unique constraint or index, case insensitive, on multiple fields
Date
Msg-id OF9DA2587D.BEB1D4A7-ON88257148.0061D884-88257148.00631CD8@CHROMALLOY.COM
Whole thread Raw
Responses Re: Unique constraint or index, case insensitive, on multiple fields  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view

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

pgsql-general by date:

Previous
From: lmyho
Date:
Subject: Debian package for freeradius_postgresql module
Next
From: Guy Fraser
Date:
Subject: Re: Backup method