Обсуждение: case insensitive unique index (part 2)

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

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

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
Index: doc/src/FAQ/FAQ.html
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/FAQ/FAQ.html,v
retrieving revision 1.66
diff -c -r1.66 FAQ.html
*** doc/src/FAQ/FAQ.html    2001/04/27 00:55:49    1.66
--- doc/src/FAQ/FAQ.html    2001/05/07 19:50:27
***************
*** 112,118 ****
       <A href="#4.11">4.11</A>) What is an R-tree index?<BR>
       <A href="#4.12">4.12</A>) What is the Genetic Query Optimizer?<BR>
       <A href="#4.13">4.13</A>) How do I perform regular expression
!     searches and case-insensitive regular expression searches?<BR>
       <A href="#4.14">4.14</A>) In a query, how do I detect if a field
      is <SMALL>NULL</SMALL>?<BR>
       <A href="#4.15">4.15</A>) What is the difference between the
--- 112,119 ----
       <A href="#4.11">4.11</A>) What is an R-tree index?<BR>
       <A href="#4.12">4.12</A>) What is the Genetic Query Optimizer?<BR>
       <A href="#4.13">4.13</A>) How do I perform regular expression
!     searches and case-insensitive regular expression searches? How do I
!     use an index for case-insensitive searches?<BR>
       <A href="#4.14">4.14</A>) In a query, how do I detect if a field
      is <SMALL>NULL</SMALL>?<BR>
       <A href="#4.15">4.15</A>) What is the difference between the
***************
*** 957,968 ****
      search.</P>

      <H4><A name="4.13">4.13</A>) How do I perform regular expression
!     searches and case-insensitive regular expression searches?</H4>

      <P>The <I>~</I> operator does regular expression matching, and
      <I>~*</I> does case-insensitive regular expression matching. The
      case-insensitive variant of <SMALL>LIKE</SMALL> is called
      <SMALL>ILIKE</SMALL> in PostgreSQL 7.1 and later.</P>

      <H4><A name="4.14">4.14</A>) In a query, how do I detect if a field
      is <SMALL>NULL</SMALL>?</H4>
--- 958,985 ----
      search.</P>

      <H4><A name="4.13">4.13</A>) How do I perform regular expression
!     searches and case-insensitive regular expression searches?  How do I
!     use an index for case-insensitive searches?</H4>

      <P>The <I>~</I> operator does regular expression matching, and
      <I>~*</I> does case-insensitive regular expression matching. The
      case-insensitive variant of <SMALL>LIKE</SMALL> is called
      <SMALL>ILIKE</SMALL> in PostgreSQL 7.1 and later.</P>
+
+     <P>Case-insensitive equality comparisons are normally expressed as:
+
+     <PRE>
+     SELECT *
+     FROM tab
+     WHERE lower(col) = 'abc'
+     </PRE>
+
+     This will not use an standard index. However, if you create a
+     functional index, it will be used:
+
+     <PRE>
+     CREATE INDEX tabindex on tab (lower(col));
+     </PRE>

      <H4><A name="4.14">4.14</A>) In a query, how do I detect if a field
      is <SMALL>NULL</SMALL>?</H4>