Debian Codegraph @

doc/src/sgml/ref/create_collation.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_collation.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createcollation">
7 <indexterm zone="sql-createcollation">
8 <primary>CREATE COLLATION</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE COLLATION</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE COLLATION</refname>
19 <refpurpose>define a new collation</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> (
25 [ LOCALE = <replaceable>locale</replaceable>, ]
26 [ LC_COLLATE = <replaceable>lc_collate</replaceable>, ]
27 [ LC_CTYPE = <replaceable>lc_ctype</replaceable>, ]
28 [ PROVIDER = <replaceable>provider</replaceable>, ]
29 [ DETERMINISTIC = <replaceable>boolean</replaceable>, ]
30 [ RULES = <replaceable>rules</replaceable>, ]
31 [ VERSION = <replaceable>version</replaceable> ]
32 )
33 CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replaceable>existing_collation</replaceable>
34 </synopsis>
35 </refsynopsisdiv>
36
37 <refsect1 id="sql-createcollation-description">
38 <title>Description</title>
39
40 <para>
41 <command>CREATE COLLATION</command> defines a new collation using
42 the specified operating system locale settings,
43 or by copying an existing collation.
44 </para>
45
46 <para>
47 To be able to create a collation, you must
48 have <literal>CREATE</literal> privilege on the destination schema.
49 </para>
50 </refsect1>
51
52
53 <refsect1>
54 <title>Parameters</title>
55
56 <variablelist>
57 <varlistentry>
58 <term><literal>IF NOT EXISTS</literal></term>
59 <listitem>
60 <para>
61 Do not throw an error if a collation with the same name already exists.
62 A notice is issued in this case. Note that there is no guarantee that
63 the existing collation is anything like the one that would have been created.
64 </para>
65 </listitem>
66 </varlistentry>
67
68 <varlistentry>
69 <term><replaceable>name</replaceable></term>
70
71 <listitem>
72 <para>
73 The name of the collation. The collation name can be
74 schema-qualified. If it is not, the collation is defined in the
75 current schema. The collation name must be unique within that
76 schema. (The system catalogs can contain collations with the
77 same name for other encodings, but these are ignored if the
78 database encoding does not match.)
79 </para>
80 </listitem>
81 </varlistentry>
82
83 <varlistentry>
84 <term><replaceable>locale</replaceable></term>
85
86 <listitem>
87 <para>
88 The locale name for this collation. See <xref
89 linkend="collation-managing-create-libc"/> and <xref
90 linkend="collation-managing-create-icu"/> for details.
91 </para>
92 <para>
93 If <replaceable>provider</replaceable> is <literal>libc</literal>, this
94 is a shortcut for setting <symbol>LC_COLLATE</symbol> and
95 <symbol>LC_CTYPE</symbol> at once. If you specify
96 <replaceable>locale</replaceable>, you cannot specify either of those
97 parameters.
98 </para>
99 <para>
100 If <replaceable>provider</replaceable> is <literal>builtin</literal>,
101 then <replaceable>locale</replaceable> must be specified and set to
102 either <literal>C</literal>, <literal>C.UTF-8</literal> or
103 <literal>PG_UNICODE_FAST</literal>.
104 </para>
105 </listitem>
106 </varlistentry>
107
108 <varlistentry>
109 <term><replaceable>lc_collate</replaceable></term>
110
111 <listitem>
112 <para>
113 If <replaceable>provider</replaceable> is <literal>libc</literal>, use
114 the specified operating system locale for the
115 <symbol>LC_COLLATE</symbol> locale category.
116 </para>
117 </listitem>
118 </varlistentry>
119
120 <varlistentry>
121 <term><replaceable>lc_ctype</replaceable></term>
122
123 <listitem>
124 <para>
125 If <replaceable>provider</replaceable> is <literal>libc</literal>, use
126 the specified operating system locale for the <symbol>LC_CTYPE</symbol>
127 locale category.
128 </para>
129 </listitem>
130 </varlistentry>
131
132 <varlistentry>
133 <term><replaceable>provider</replaceable></term>
134
135 <listitem>
136 <para>
137 Specifies the provider to use for locale services associated with this
138 collation. Possible values are <literal>builtin</literal>,
139 <literal>icu</literal><indexterm><primary>ICU</primary></indexterm> (if
140 the server was built with ICU support) or <literal>libc</literal>.
141 <literal>libc</literal> is the default. See <xref
142 linkend="locale-providers"/> for details.
143 </para>
144 </listitem>
145 </varlistentry>
146
147 <varlistentry>
148 <term><literal>DETERMINISTIC</literal></term>
149
150 <listitem>
151 <para>
152 Specifies whether the collation should use deterministic comparisons.
153 The default is true. A deterministic comparison considers strings that
154 are not byte-wise equal to be unequal even if they are considered
155 logically equal by the comparison. PostgreSQL breaks ties using a
156 byte-wise comparison. Comparison that is not deterministic can make the
157 collation be, say, case- or accent-insensitive. For that, you need to
158 choose an appropriate <literal>LOCALE</literal> setting
159 <emphasis>and</emphasis> set the collation to not deterministic here.
160 </para>
161
162 <para>
163 Nondeterministic collations are only supported with the ICU provider.
164 </para>
165 </listitem>
166 </varlistentry>
167
168 <varlistentry>
169 <term><replaceable>rules</replaceable></term>
170
171 <listitem>
172 <para>
173 Specifies additional collation rules to customize the behavior of the
174 collation. This is supported for ICU only. See <xref
175 linkend="icu-tailoring-rules"/> for details.
176 </para>
177 </listitem>
178 </varlistentry>
179
180 <varlistentry>
181 <term><replaceable>version</replaceable></term>
182
183 <listitem>
184 <para>
185 Specifies the version string to store with the collation. Normally,
186 this should be omitted, which will cause the version to be computed
187 from the actual version of the collation as provided by the operating
188 system. This option is intended to be used
189 by <command>pg_upgrade</command> for copying the version from an
190 existing installation.
191 </para>
192
193 <para>
194 See also <xref linkend="sql-altercollation"/> for how to handle
195 collation version mismatches.
196 </para>
197 </listitem>
198 </varlistentry>
199
200 <varlistentry>
201 <term><replaceable>existing_collation</replaceable></term>
202
203 <listitem>
204 <para>
205 The name of an existing collation to copy. The new collation
206 will have the same properties as the existing one, but it
207 will be an independent object.
208 </para>
209 </listitem>
210 </varlistentry>
211 </variablelist>
212 </refsect1>
213
214
215 <refsect1 id="sql-createcollation-notes">
216 <title>Notes</title>
217
218 <para>
219 <command>CREATE COLLATION</command> takes a <literal>SHARE ROW
220 EXCLUSIVE</literal> lock, which is self-conflicting, on the
221 <structname>pg_collation</structname> system catalog, so only one
222 <command>CREATE COLLATION</command> command can run at a time.
223 </para>
224
225 <para>
226 Use <command>DROP COLLATION</command> to remove user-defined collations.
227 </para>
228
229 <para>
230 See <xref linkend="collation-create"/> for more information on how to create collations.
231 </para>
232
233 <para>
234 When using the <literal>libc</literal> collation provider, the locale must
235 be applicable to the current database encoding.
236 See <xref linkend="sql-createdatabase"/> for the precise rules.
237 </para>
238 </refsect1>
239
240 <refsect1 id="sql-createcollation-examples">
241 <title>Examples</title>
242
243 <para>
244 To create a collation from the operating system locale
245 <literal>fr_FR.utf8</literal>
246 (assuming the current database encoding is <literal>UTF8</literal>):
247 <programlisting>
248 CREATE COLLATION french (locale = 'fr_FR.utf8');
249 </programlisting>
250 </para>
251
252 <para>
253 To create a collation using the ICU provider using German phone book sort order:
254 <programlisting>
255 CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');
256 </programlisting>
257 </para>
258
259 <para>
260 To create a collation using the ICU provider, based on the root ICU locale,
261 with custom rules:
262 <programlisting>
263 <![CDATA[CREATE COLLATION custom (provider = icu, locale = 'und', rules = '&V << w <<< W');]]>
264 </programlisting>
265 See <xref linkend="icu-tailoring-rules"/> for further details and examples
266 on the rules syntax.
267 </para>
268
269 <para>
270 To create a collation from an existing collation:
271 <programlisting>
272 CREATE COLLATION german FROM "de_DE";
273 </programlisting>
274 This can be convenient to be able to use operating-system-independent
275 collation names in applications.
276 </para>
277 </refsect1>
278
279
280 <refsect1 id="sql-createcollation-compat">
281 <title>Compatibility</title>
282
283 <para>
284 There is a <command>CREATE COLLATION</command> statement in the SQL
285 standard, but it is limited to copying an existing collation. The
286 syntax to create a new collation is
287 a <productname>PostgreSQL</productname> extension.
288 </para>
289 </refsect1>
290
291
292 <refsect1 id="sql-createcollation-seealso">
293 <title>See Also</title>
294
295 <simplelist type="inline">
296 <member><xref linkend="sql-altercollation"/></member>
297 <member><xref linkend="sql-dropcollation"/></member>
298 </simplelist>
299 </refsect1>
300
301 </refentry>