|
1 | | -<!-- $PostgreSQL: pgsql/doc/src/sgml/citext.sgml,v 1.1 2008/07/29 18:31:20 tgl Exp $ --> |
| 1 | +<!-- $PostgreSQL: pgsql/doc/src/sgml/citext.sgml,v 1.2 2008/09/12 18:29:49 tgl Exp $ --> |
2 | 2 |
|
3 | 3 | <sect1 id="citext"> |
4 | 4 | <title>citext</title> |
|
95 | 95 | </para> |
96 | 96 | </sect2> |
97 | 97 |
|
| 98 | + <sect2> |
| 99 | + <title>String Comparison Behavior</title> |
| 100 | + <para> |
| 101 | + In order to emulate a case-insensitive collation as closely as possible, |
| 102 | + there are <type>citext</>-specific versions of a number of the comparison |
| 103 | + operators and functions. So, for example, the regular expression |
| 104 | + operators <literal>~</> and <literal>~*</> exhibit the same behavior when |
| 105 | + applied to <type>citext</>: they both compare case-insensitively. |
| 106 | + The same is true |
| 107 | + for <literal>!~</> and <literal>!~*</>, as well as for the |
| 108 | + <literal>LIKE</> operators <literal>~~</> and <literal>~~*</>, and |
| 109 | + <literal>!~~</> and <literal>!~~*</>. If you'd like to match |
| 110 | + case-sensitively, you can always cast to <type>text</> before comparing. |
| 111 | + </para> |
| 112 | + |
| 113 | + <para> |
| 114 | + Similarly, all of the following functions perform matching |
| 115 | + case-insensitively if their arguments are <type>citext</>: |
| 116 | + </para> |
| 117 | + |
| 118 | + <itemizedlist> |
| 119 | + <listitem> |
| 120 | + <para> |
| 121 | + <function>regexp_replace()</> |
| 122 | + </para> |
| 123 | + </listitem> |
| 124 | + <listitem> |
| 125 | + <para> |
| 126 | + <function>regexp_split_to_array()</> |
| 127 | + </para> |
| 128 | + </listitem> |
| 129 | + <listitem> |
| 130 | + <para> |
| 131 | + <function>regexp_split_to_table()</> |
| 132 | + </para> |
| 133 | + </listitem> |
| 134 | + <listitem> |
| 135 | + <para> |
| 136 | + <function>replace()</> |
| 137 | + </para> |
| 138 | + </listitem> |
| 139 | + <listitem> |
| 140 | + <para> |
| 141 | + <function>split_part()</> |
| 142 | + </para> |
| 143 | + </listitem> |
| 144 | + <listitem> |
| 145 | + <para> |
| 146 | + <function>strpos()</> |
| 147 | + </para> |
| 148 | + </listitem> |
| 149 | + <listitem> |
| 150 | + <para> |
| 151 | + <function>translate()</> |
| 152 | + </para> |
| 153 | + </listitem> |
| 154 | + </itemizedlist> |
| 155 | + |
| 156 | + <para> |
| 157 | + For the regexp functions, if you want to match case-sensitively, you can |
| 158 | + specify the <quote>c</> flag to force a case-sensitive match. Otherwise, |
| 159 | + you must cast to <type>text</> before using one of these functions if |
| 160 | + you want case-sensitive behavior. |
| 161 | + </para> |
| 162 | + |
| 163 | + </sect2> |
| 164 | + |
98 | 165 | <sect2> |
99 | 166 | <title>Limitations</title> |
100 | 167 |
|
|
114 | 181 | </para> |
115 | 182 | </listitem> |
116 | 183 |
|
117 | | - <listitem> |
118 | | - <para> |
119 | | - The pattern-matching comparison operators, such as <literal>LIKE</>, |
120 | | - <literal>~</>, <literal>~~</>, <literal>!~</>, <literal>!~~</>, etc., |
121 | | - have been overloaded to make case-insensitive comparisons when their |
122 | | - left-hand argument is of type <type>citext</>. However, related |
123 | | - functions have not been changed, including: |
124 | | - </para> |
125 | | - |
126 | | - <itemizedlist> |
127 | | - <listitem> |
128 | | - <para> |
129 | | - <function>regexp_replace()</> |
130 | | - </para> |
131 | | - </listitem> |
132 | | - <listitem> |
133 | | - <para> |
134 | | - <function>regexp_split_to_array()</> |
135 | | - </para> |
136 | | - </listitem> |
137 | | - <listitem> |
138 | | - <para> |
139 | | - <function>regexp_split_to_table()</> |
140 | | - </para> |
141 | | - </listitem> |
142 | | - <listitem> |
143 | | - <para> |
144 | | - <function>replace()</> |
145 | | - </para> |
146 | | - </listitem> |
147 | | - <listitem> |
148 | | - <para> |
149 | | - <function>split_part()</> |
150 | | - </para> |
151 | | - </listitem> |
152 | | - <listitem> |
153 | | - <para> |
154 | | - <function>strpos()</> |
155 | | - </para> |
156 | | - </listitem> |
157 | | - <listitem> |
158 | | - <para> |
159 | | - <function>translate()</> |
160 | | - </para> |
161 | | - </listitem> |
162 | | - </itemizedlist> |
163 | | - |
164 | | - <para> |
165 | | - Of course, for the regular expression functions, you can specify |
166 | | - case-insensitive comparisons in their <parameter>flags</> arguments, but |
167 | | - the same cannot be done for the the non-regexp functions. |
168 | | - </para> |
169 | | - </listitem> |
170 | | - |
171 | 184 | <listitem> |
172 | 185 | <para> |
173 | 186 | <type>citext</> is not as efficient as <type>text</> because the |
|
178 | 191 | </para> |
179 | 192 | </listitem> |
180 | 193 |
|
181 | | - <listitem> |
182 | | - <para> |
183 | | - <productname>PostgreSQL</> supports casting between <type>text</> |
184 | | - and any other type (even non-string types) by using the other type's |
185 | | - I/O functions. This doesn't work with <type>citext</>. However, |
186 | | - you can cast via I/O functions in two steps, for example |
187 | | - <literal><replaceable>somevalue</>::text::citext</literal> or |
188 | | - <literal><replaceable>citextvalue</>::text::<replaceable>sometype</></literal>. |
189 | | - </para> |
190 | | - </listitem> |
191 | | - |
192 | 194 | <listitem> |
193 | 195 | <para> |
194 | 196 | <type>citext</> doesn't help much if you need data to compare |
|