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