codekingpro/portable-devtools
115k
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.10. 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.9. btree_gist — GiST operator classes with B-tree behavior" /><link rel="next" href="cube.html" title="F.11. 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.10. citext — a case-insensitive character string type</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="btree-gist.html" title="F.9. 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 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="cube.html" title="F.11. 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.10. 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.10.1. Rationale</a></span></dt><dt><span class="sect2"><a href="citext.html#CITEXT-HOW-TO-USE-IT">F.10.2. How to Use It</a></span></dt><dt><span class="sect2"><a href="citext.html#CITEXT-STRING-COMPARISON-BEHAVIOR">F.10.3. String Comparison Behavior</a></span></dt><dt><span class="sect2"><a href="citext.html#CITEXT-LIMITATIONS">F.10.4. Limitations</a></span></dt><dt><span class="sect2"><a href="citext.html#CITEXT-AUTHOR">F.10.5. Author</a></span></dt></dl></div><a id="id-1.11.7.20.2" class="indexterm"></a><p>3 The <code class="filename">citext</code> module provides a case-insensitive4 character string type, <code class="type">citext</code>. Essentially, it internally calls5 <code class="function">lower</code> when comparing values. Otherwise, it behaves almost6 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> (see9 <a class="xref" href="collation.html#COLLATION-NONDETERMINISTIC" title="24.2.2.4. Nondeterministic Collations">Section 24.2.2.4</a>) instead of this module. They10 can be used for case-insensitive comparisons, accent-insensitive11 comparisons, and other combinations, and they handle more Unicode special12 cases correctly.13 </p></div><p>14 This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be15 installed by non-superusers who have <code class="literal">CREATE</code> privilege16 on the current database.17 </p><div class="sect2" id="CITEXT-RATIONALE"><div class="titlepage"><div><div><h3 class="title">F.10.1. Rationale <a href="#CITEXT-RATIONALE" class="id_link">#</a></h3></div></div></div><p>18 The standard approach to doing case-insensitive matches19 in <span class="productname">PostgreSQL</span> has been to use the <code class="function">lower</code>20 function when comparing values, for example21 22</p><pre class="programlisting">23SELECT * 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 to29 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 using32 <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">PRIMARY35 KEY</code>, the implicitly generated index is case-sensitive. So it's36 useless for case-insensitive searches, and it won't enforce37 uniqueness case-insensitively.38 </p></li></ul></div><p>39 The <code class="type">citext</code> data type allows you to eliminate calls40 to <code class="function">lower</code> in SQL queries, and allows a primary key to41 be case-insensitive. <code class="type">citext</code> is locale-aware, just42 like <code class="type">text</code>, which means that the matching of upper case and43 lower case characters is dependent on the rules of44 the database's <code class="literal">LC_CTYPE</code> setting. Again, this behavior is45 identical to the use of <code class="function">lower</code> in queries. But because it's46 done transparently by the data type, you don't have to remember to do47 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.10.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">52CREATE TABLE users (53 nick CITEXT PRIMARY KEY,54 pass TEXT NOT NULL55);56 57INSERT INTO users VALUES ( 'larry', sha256(random()::text::bytea) );58INSERT INTO users VALUES ( 'Tom', sha256(random()::text::bytea) );59INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );60INSERT INTO users VALUES ( 'NEAL', sha256(random()::text::bytea) );61INSERT INTO users VALUES ( 'Bjørn', sha256(random()::text::bytea) );62 63SELECT * FROM users WHERE nick = 'Larry';64</pre><p>65 66 The <code class="command">SELECT</code> statement will return one tuple, even though67 the <code class="structfield">nick</code> column was set to <code class="literal">larry</code> and the query68 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.10.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 lower71 case (as though <code class="function">lower</code> were called) and then comparing the72 results normally. Thus, for example, two strings are considered equal73 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-processing77 operators and functions. So, for example, the regular expression78 operators <code class="literal">~</code> and <code class="literal">~*</code> exhibit the same behavior when79 applied to <code class="type">citext</code>: they both match case-insensitively.80 The same is true81 for <code class="literal">!~</code> and <code class="literal">!~*</code>, as well as for the82 <code class="literal">LIKE</code> operators <code class="literal">~~</code> and <code class="literal">~~*</code>, and83 <code class="literal">!~~</code> and <code class="literal">!~~*</code>. If you'd like to match84 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 matching87 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 can108 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 if110 you want case-sensitive behavior.111 </p></div><div class="sect2" id="CITEXT-LIMITATIONS"><div class="titlepage"><div><div><h3 class="title">F.10.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 on113 the <code class="literal">LC_CTYPE</code> setting of your database. How it compares114 values is therefore determined when the database is created.115 It is not truly116 case-insensitive in the terms defined by the Unicode standard.117 Effectively, what this means is that, as long as you're happy with your118 collation, you should be happy with <code class="type">citext</code>'s comparisons. But119 if you have data in different languages stored in your database, users120 of one language may find their query results are not as expected if the121 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 a124 <code class="literal">COLLATE</code> specification to <code class="type">citext</code> columns or data125 values. Currently, <code class="type">citext</code> operators will honor a non-default126 <code class="literal">COLLATE</code> specification while comparing case-folded strings,127 but the initial folding to lower case is always done according to the128 database's <code class="literal">LC_CTYPE</code> setting (that is, as though129 <code class="literal">COLLATE "default"</code> were given). This may be changed in a130 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 the134 operator functions and the B-tree comparison functions must make copies135 of the data and convert it to lower case for comparisons. Also, only136 <code class="type">text</code> can support B-Tree deduplication. However,137 <code class="type">citext</code> is slightly more efficient than using138 <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 compare141 case-sensitively in some contexts and case-insensitively in other142 contexts. The standard answer is to use the <code class="type">text</code> type and143 manually use the <code class="function">lower</code> function when you need to compare144 case-insensitively; this works all right if case-insensitive comparison145 is needed only infrequently. If you need case-insensitive behavior most146 of the time and case-sensitive infrequently, consider storing the data147 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 will149 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 be152 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> operators154 will be invoked instead.155 </p></li><li class="listitem"><p>156 The approach of lower-casing strings for comparison does not handle some157 Unicode special cases correctly, for example when one upper-case letter158 has two lower-case letter equivalents. Unicode distinguishes between159 <em class="firstterm">case mapping</em> and <em class="firstterm">case160 folding</em> for this reason. Use nondeterministic collations161 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.10.5. Author <a href="#CITEXT-AUTHOR" class="id_link">#</a></h3></div></div></div><p>163 David E. Wheeler <code class="email"><<a class="email" href="mailto:david@kineticode.com">david@kineticode.com</a>></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.9. 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.11. cube — a multi-dimensional cube data type">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.9. btree_gist — GiST operator classes with B-tree behavior </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.11. cube — a multi-dimensional cube data type</td></tr></table></div></body></html>