Re: case insensitive unique index (part 2)

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>