Re: [GENERAL] case insensitive unique index (part 2)

Can someone suggest an addition to the FAQ for this?  Does ILIKE, ~* use
such indexes automatically?


> Hi,
> 
> I've trolled the archives and the FAQ and the closest I could come up with was
> the following mailing list message:
> 
>   http://www.postgresql.org/mhonarc/pgsql-general/2001-01/msg01632.html
> 
> which advises that you can do something like this to create a case-insensitive
> unique index:
> 
>   CREATE UNIQUE INDEX indexname ON tablename (lower(fieldname))
> 
> however, that doesn't work under 7.0.2 at least, I get:
> 
> mydb=> create unique index forward_rr on forward (lower(name));
> ERROR:  DefineIndex: function 'lower(varchar)' does not exist
> 
> Futhermore, I want to create the case-insensitive unique index on a tuple of
> varchar fields, ie.:
> 
>   CREATE UNIQUE INDEX indexname ON tablename (lower(field1), lower(field2), lower(field3))
> 
> however, looking at the docs at:
> 
>   http://www.postgresql.org/users-lounge/docs/7.0/user/sql-createindex.htm
> 
> it looks like I will need to instead declare a function f such that it can be used as:
> 
>   CREATE UNIQUE INDEX indexname ON tablename (f(field1, field2, field3))
> 
> and f(n1, n2, n3) returns a unique value for unique combinations of n1, n2 and
> n3...
> 
> Since this sounds like something someone must have already done, or should be
> a FAQ, any hints? pointers? functions? PL/SQL code?
> 
> Thanks,
> Adi
> 
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026