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