From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | lists(at)lists(dot)grot(dot)org |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [GENERAL] case insensitive unique index (part 2) |
Date: | 2001-03-19 22:06:49 |
Message-ID: | 200103192206.RAA05893@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
From | Date | Subject | |
---|---|---|---|
Next Message | Rick Delaney | 2001-03-19 22:10:45 | Accessing serials through rules |
Previous Message | Jan Ploski | 2001-03-19 20:39:43 | Re: concurrent updates problem |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-03-19 22:37:33 | src/test/regress/README duplicates SGML material |
Previous Message | Ian Lance Taylor | 2001-03-19 21:34:14 | Re: Patch application |