begriffs open source - ai-pg/blob - full-docs/html/citext.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.9. citext — a case-insensitive character string type</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="btree-gist.html" title="F.8. btree_gist — GiST operator classes with B-tree behavior" /><link rel="next" href="cube.html" title="F.10. cube — a multi-dimensional cube data type" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.9. citext — a case-insensitive character string type</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="btree-gist.html" title="F.8. btree_gist — GiST operator classes with B-tree behavior">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="cube.html" title="F.10. cube — a multi-dimensional cube data type">Next</a></td></tr></table><hr /></div><div class="sect1" id="CITEXT"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.9. citext — a case-insensitive character string type <a href="#CITEXT" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="citext.html#CITEXT-RATIONALE">F.9.1. Rationale</a></span></dt><dt><span class="sect2"><a href="citext.html#CITEXT-HOW-TO-USE-IT">F.9.2. How to Use It</a></span></dt><dt><span class="sect2"><a href="citext.html#CITEXT-STRING-COMPARISON-BEHAVIOR">F.9.3. String Comparison Behavior</a></span></dt><dt><span class="sect2"><a href="citext.html#CITEXT-LIMITATIONS">F.9.4. Limitations</a></span></dt><dt><span class="sect2"><a href="citext.html#CITEXT-AUTHOR">F.9.5. Author</a></span></dt></dl></div><a id="id-1.11.7.19.2" class="indexterm"></a><p>
   3   The <code class="filename">citext</code> module provides a case-insensitive
   4   character string type, <code class="type">citext</code>. Essentially, it internally calls
   5   <code class="function">lower</code> when comparing values. Otherwise, it behaves almost
   6   exactly like <code class="type">text</code>.
   7  </p><div class="tip"><h3 class="title">Tip</h3><p>
   8    Consider using <em class="firstterm">nondeterministic collations</em> (see
   9    <a class="xref" href="collation.html#COLLATION-NONDETERMINISTIC" title="23.2.2.4. Nondeterministic Collations">Section 23.2.2.4</a>) instead of this module.  They
  10    can be used for case-insensitive comparisons, accent-insensitive
  11    comparisons, and other combinations, and they handle more Unicode special
  12    cases correctly.
  13   </p></div><p>
  14   This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be
  15   installed by non-superusers who have <code class="literal">CREATE</code> privilege
  16   on the current database.
  17  </p><div class="sect2" id="CITEXT-RATIONALE"><div class="titlepage"><div><div><h3 class="title">F.9.1. Rationale <a href="#CITEXT-RATIONALE" class="id_link">#</a></h3></div></div></div><p>
  18    The standard approach to doing case-insensitive matches
  19    in <span class="productname">PostgreSQL</span> has been to use the <code class="function">lower</code>
  20    function when comparing values, for example
  21
  22 </p><pre class="programlisting">
  23 SELECT * FROM tab WHERE lower(col) = LOWER(?);
  24 </pre><p>
  25   </p><p>
  26    This works reasonably well, but has a number of drawbacks:
  27   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
  28       It makes your SQL statements verbose, and you always have to remember to
  29       use <code class="function">lower</code> on both the column and the query value.
  30      </p></li><li class="listitem"><p>
  31       It won't use an index, unless you create a functional index using
  32       <code class="function">lower</code>.
  33      </p></li><li class="listitem"><p>
  34       If you declare a column as <code class="literal">UNIQUE</code> or <code class="literal">PRIMARY
  35       KEY</code>, the implicitly generated index is case-sensitive.  So it's
  36       useless for case-insensitive searches, and it won't enforce
  37       uniqueness case-insensitively.
  38      </p></li></ul></div><p>
  39     The <code class="type">citext</code> data type allows you to eliminate calls
  40     to <code class="function">lower</code> in SQL queries, and allows a primary key to
  41     be case-insensitive. <code class="type">citext</code> is locale-aware, just
  42     like <code class="type">text</code>, which means that the matching of upper case and
  43     lower case characters is dependent on the rules of
  44     the database's <code class="literal">LC_CTYPE</code> setting. Again, this behavior is
  45     identical to the use of <code class="function">lower</code> in queries. But because it's
  46     done transparently by the data type, you don't have to remember to do
  47     anything special in your queries.
  48    </p></div><div class="sect2" id="CITEXT-HOW-TO-USE-IT"><div class="titlepage"><div><div><h3 class="title">F.9.2. How to Use It <a href="#CITEXT-HOW-TO-USE-IT" class="id_link">#</a></h3></div></div></div><p>
  49    Here's a simple example of usage:
  50
  51 </p><pre class="programlisting">
  52 CREATE TABLE users (
  53     nick CITEXT PRIMARY KEY,
  54     pass TEXT   NOT NULL
  55 );
  56
  57 INSERT INTO users VALUES ( 'larry',  sha256(random()::text::bytea) );
  58 INSERT INTO users VALUES ( 'Tom',    sha256(random()::text::bytea) );
  59 INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
  60 INSERT INTO users VALUES ( 'NEAL',   sha256(random()::text::bytea) );
  61 INSERT INTO users VALUES ( 'Bjørn',  sha256(random()::text::bytea) );
  62
  63 SELECT * FROM users WHERE nick = 'Larry';
  64 </pre><p>
  65
  66    The <code class="command">SELECT</code> statement will return one tuple, even though
  67    the <code class="structfield">nick</code> column was set to <code class="literal">larry</code> and the query
  68    was for <code class="literal">Larry</code>.
  69   </p></div><div class="sect2" id="CITEXT-STRING-COMPARISON-BEHAVIOR"><div class="titlepage"><div><div><h3 class="title">F.9.3. String Comparison Behavior <a href="#CITEXT-STRING-COMPARISON-BEHAVIOR" class="id_link">#</a></h3></div></div></div><p>
  70    <code class="type">citext</code> performs comparisons by converting each string to lower
  71    case (as though <code class="function">lower</code> were called) and then comparing the
  72    results normally.  Thus, for example, two strings are considered equal
  73    if <code class="function">lower</code> would produce identical results for them.
  74   </p><p>
  75    In order to emulate a case-insensitive collation as closely as possible,
  76    there are <code class="type">citext</code>-specific versions of a number of string-processing
  77    operators and functions.  So, for example, the regular expression
  78    operators <code class="literal">~</code> and <code class="literal">~*</code> exhibit the same behavior when
  79    applied to <code class="type">citext</code>: they both match case-insensitively.
  80    The same is true
  81    for <code class="literal">!~</code> and <code class="literal">!~*</code>, as well as for the
  82    <code class="literal">LIKE</code> operators <code class="literal">~~</code> and <code class="literal">~~*</code>, and
  83    <code class="literal">!~~</code> and <code class="literal">!~~*</code>. If you'd like to match
  84    case-sensitively, you can cast the operator's arguments to <code class="type">text</code>.
  85   </p><p>
  86    Similarly, all of the following functions perform matching
  87    case-insensitively if their arguments are <code class="type">citext</code>:
  88   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
  89       <code class="function">regexp_match()</code>
  90     </p></li><li class="listitem"><p>
  91       <code class="function">regexp_matches()</code>
  92     </p></li><li class="listitem"><p>
  93       <code class="function">regexp_replace()</code>
  94     </p></li><li class="listitem"><p>
  95       <code class="function">regexp_split_to_array()</code>
  96     </p></li><li class="listitem"><p>
  97       <code class="function">regexp_split_to_table()</code>
  98     </p></li><li class="listitem"><p>
  99       <code class="function">replace()</code>
 100     </p></li><li class="listitem"><p>
 101       <code class="function">split_part()</code>
 102     </p></li><li class="listitem"><p>
 103       <code class="function">strpos()</code>
 104     </p></li><li class="listitem"><p>
 105       <code class="function">translate()</code>
 106     </p></li></ul></div><p>
 107    For the regexp functions, if you want to match case-sensitively, you can
 108    specify the <span class="quote">“<span class="quote">c</span>”</span> flag to force a case-sensitive match.  Otherwise,
 109    you must cast to <code class="type">text</code> before using one of these functions if
 110    you want case-sensitive behavior.
 111   </p></div><div class="sect2" id="CITEXT-LIMITATIONS"><div class="titlepage"><div><div><h3 class="title">F.9.4. Limitations <a href="#CITEXT-LIMITATIONS" class="id_link">#</a></h3></div></div></div><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
 112       <code class="type">citext</code>'s case-folding behavior depends on
 113       the <code class="literal">LC_CTYPE</code> setting of your database. How it compares
 114       values is therefore determined when the database is created.
 115       It is not truly
 116       case-insensitive in the terms defined by the Unicode standard.
 117       Effectively, what this means is that, as long as you're happy with your
 118       collation, you should be happy with <code class="type">citext</code>'s comparisons. But
 119       if you have data in different languages stored in your database, users
 120       of one language may find their query results are not as expected if the
 121       collation is for another language.
 122      </p></li><li class="listitem"><p>
 123       As of <span class="productname">PostgreSQL</span> 9.1, you can attach a
 124       <code class="literal">COLLATE</code> specification to <code class="type">citext</code> columns or data
 125       values.  Currently, <code class="type">citext</code> operators will honor a non-default
 126       <code class="literal">COLLATE</code> specification while comparing case-folded strings,
 127       but the initial folding to lower case is always done according to the
 128       database's <code class="literal">LC_CTYPE</code> setting (that is, as though
 129       <code class="literal">COLLATE "default"</code> were given).  This may be changed in a
 130       future release so that both steps follow the input <code class="literal">COLLATE</code>
 131       specification.
 132      </p></li><li class="listitem"><p>
 133        <code class="type">citext</code> is not as efficient as <code class="type">text</code> because the
 134        operator functions and the B-tree comparison functions must make copies
 135        of the data and convert it to lower case for comparisons.  Also, only
 136        <code class="type">text</code> can support B-Tree deduplication.  However,
 137        <code class="type">citext</code> is slightly more efficient than using
 138        <code class="function">lower</code> to get case-insensitive matching.
 139      </p></li><li class="listitem"><p>
 140       <code class="type">citext</code> doesn't help much if you need data to compare
 141       case-sensitively in some contexts and case-insensitively in other
 142       contexts.  The standard answer is to use the <code class="type">text</code> type and
 143       manually use the <code class="function">lower</code> function when you need to compare
 144       case-insensitively; this works all right if case-insensitive comparison
 145       is needed only infrequently.  If you need case-insensitive behavior most
 146       of the time and case-sensitive infrequently, consider storing the data
 147       as <code class="type">citext</code> and explicitly casting the column to <code class="type">text</code>
 148       when you want case-sensitive comparison.  In either situation, you will
 149       need two indexes if you want both types of searches to be fast.
 150     </p></li><li class="listitem"><p>
 151       The schema containing the <code class="type">citext</code> operators must be
 152       in the current <code class="varname">search_path</code> (typically <code class="literal">public</code>);
 153       if it is not, the normal case-sensitive <code class="type">text</code> operators
 154       will be invoked instead.
 155     </p></li><li class="listitem"><p>
 156       The approach of lower-casing strings for comparison does not handle some
 157       Unicode special cases correctly, for example when one upper-case letter
 158       has two lower-case letter equivalents.  Unicode distinguishes between
 159       <em class="firstterm">case mapping</em> and <em class="firstterm">case
 160       folding</em> for this reason.  Use nondeterministic collations
 161       instead of <code class="type">citext</code> to handle that correctly.
 162      </p></li></ul></div></div><div class="sect2" id="CITEXT-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.9.5. Author <a href="#CITEXT-AUTHOR" class="id_link">#</a></h3></div></div></div><p>
 163    David E. Wheeler <code class="email">&lt;<a class="email" href="mailto:david@kineticode.com">david@kineticode.com</a>&gt;</code>
 164   </p><p>
 165     Inspired by the original <code class="type">citext</code> module by Donald Fraser.
 166   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="btree-gist.html" title="F.8. btree_gist — GiST operator classes with B-tree behavior">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="cube.html" title="F.10. cube — a multi-dimensional cube data type">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.8. btree_gist — GiST operator classes with B-tree behavior </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.10. cube — a multi-dimensional cube data type</td></tr></table></div></body></html>