case insensitive unique index (part 2)

lists <lists@lists.grot.org> writes:

mydb=> create unique index forward_rr on forward (lower(name));
ERROR: DefineIndex: function 'lower(varchar)' does not exist

If you change the name column to type 'text' it will work. Or you
can make an extra pg_proc entry that relabels lower(text) as
lower(varchar) --- that's safe because text and varchar have the
same representation. I'm too lazy to reconstruct the necessary
command right now, but this has been described in the mail lists before.

regards, tom lane

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, Pennsylvania 19026

I have added the following patch to the FAQ to mention the use of
functional indexes for case-insensitive equality comparisons.

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, Pennsylvania 19026