1<!--
2doc/src/sgml/ref/create_collation.sgml
3PostgreSQL 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>
24CREATE 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    [ VERSION = <replaceable>version</replaceable> ]
30)
31CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replaceable>existing_collation</replaceable>
32</synopsis>
33 </refsynopsisdiv>
34
35 <refsect1 id="sql-createcollation-description">
36  <title>Description</title>
37
38  <para>
39   <command>CREATE COLLATION</command> defines a new collation using
40   the specified operating system locale settings,
41   or by copying an existing collation.
42 </para>
43
44  <para>
45   To be able to create a collation, you must
46   have <literal>CREATE</literal> privilege on the destination schema.
47  </para>
48 </refsect1>
49
50
51 <refsect1>
52  <title>Parameters</title>
53
54   <variablelist>
55    <varlistentry>
56     <term><literal>IF NOT EXISTS</literal></term>
57     <listitem>
58      <para>
59       Do not throw an error if a collation with the same name already exists.
60       A notice is issued in this case. Note that there is no guarantee that
61       the existing collation is anything like the one that would have been created.
62      </para>
63     </listitem>
64    </varlistentry>
65
66    <varlistentry>
67     <term><replaceable>name</replaceable></term>
68
69     <listitem>
70      <para>
71       The name of the collation. The collation name can be
72       schema-qualified. If it is not, the collation is defined in the
73       current schema. The collation name must be unique within that
74       schema.  (The system catalogs can contain collations with the
75       same name for other encodings, but these are ignored if the
76       database encoding does not match.)
77      </para>
78     </listitem>
79    </varlistentry>
80
81    <varlistentry>
82     <term><replaceable>locale</replaceable></term>
83
84     <listitem>
85      <para>
86       This is a shortcut for setting <symbol>LC_COLLATE</symbol>
87       and <symbol>LC_CTYPE</symbol> at once.  If you specify this,
88       you cannot specify either of those parameters.
89      </para>
90     </listitem>
91    </varlistentry>
92
93    <varlistentry>
94     <term><replaceable>lc_collate</replaceable></term>
95
96     <listitem>
97      <para>
98       Use the specified operating system locale for
99       the <symbol>LC_COLLATE</symbol> locale category.
100      </para>
101     </listitem>
102    </varlistentry>
103
104    <varlistentry>
105     <term><replaceable>lc_ctype</replaceable></term>
106
107     <listitem>
108      <para>
109       Use the specified operating system locale for
110       the <symbol>LC_CTYPE</symbol> locale category.
111      </para>
112     </listitem>
113    </varlistentry>
114
115    <varlistentry>
116     <term><replaceable>provider</replaceable></term>
117
118     <listitem>
119      <para>
120       Specifies the provider to use for locale services associated with this
121       collation.  Possible values
122       are: <literal>icu</literal>,<indexterm><primary>ICU</></>
123       <literal>libc</literal>.
124       <literal>libc</literal> is the default.
125       The available choices depend on the operating system and build options.
126      </para>
127     </listitem>
128    </varlistentry>
129
130    <varlistentry>
131     <term><replaceable>version</replaceable></term>
132
133     <listitem>
134      <para>
135       Specifies the version string to store with the collation.  Normally,
136       this should be omitted, which will cause the version to be computed
137       from the actual version of the collation as provided by the operating
138       system.  This option is intended to be used
139       by <command>pg_upgrade</command> for copying the version from an
140       existing installation.
141      </para>
142
143      <para>
144       See also <xref linkend="sql-altercollation"> for how to handle
145       collation version mismatches.
146      </para>
147     </listitem>
148    </varlistentry>
149
150    <varlistentry>
151     <term><replaceable>existing_collation</replaceable></term>
152
153     <listitem>
154      <para>
155       The name of an existing collation to copy.  The new collation
156       will have the same properties as the existing one, but it
157       will be an independent object.
158      </para>
159     </listitem>
160    </varlistentry>
161   </variablelist>
162 </refsect1>
163
164
165 <refsect1 id="sql-createcollation-notes">
166  <title>Notes</title>
167
168  <para>
169   Use <command>DROP COLLATION</command> to remove user-defined collations.
170  </para>
171
172  <para>
173   See <xref linkend="collation-create"> for more information on how to create collations.
174  </para>
175
176  <para>
177   When using the <literal>libc</literal> collation provider, the locale must
178   be applicable to the current database encoding.
179   See <xref linkend="sql-createdatabase"> for the precise rules.
180  </para>
181 </refsect1>
182
183 <refsect1 id="sql-createcollation-examples">
184  <title>Examples</title>
185
186  <para>
187   To create a collation from the operating system locale
188   <literal>fr_FR.utf8</literal>
189   (assuming the current database encoding is <literal>UTF8</literal>):
190<programlisting>
191CREATE COLLATION french (locale = 'fr_FR.utf8');
192</programlisting>
193  </para>
194
195  <para>
196   To create a collation using the ICU provider using German phone book sort order:
197<programlisting>
198CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');
199</programlisting>
200  </para>
201
202  <para>
203   To create a collation from an existing collation:
204<programlisting>
205CREATE COLLATION german FROM "de_DE";
206</programlisting>
207   This can be convenient to be able to use operating-system-independent
208   collation names in applications.
209  </para>
210 </refsect1>
211
212
213 <refsect1 id="sql-createcollation-compat">
214  <title>Compatibility</title>
215
216  <para>
217   There is a <command>CREATE COLLATION</command> statement in the SQL
218   standard, but it is limited to copying an existing collation.  The
219   syntax to create a new collation is
220   a <productname>PostgreSQL</productname> extension.
221  </para>
222 </refsect1>
223
224
225 <refsect1 id="sql-createcollation-seealso">
226  <title>See Also</title>
227
228  <simplelist type="inline">
229   <member><xref linkend="sql-altercollation"></member>
230   <member><xref linkend="sql-dropcollation"></member>
231  </simplelist>
232 </refsect1>
233
234</refentry>
235