1<!-- 2doc/src/sgml/ref/alter_collation.sgml 3PostgreSQL documentation 4--> 5 6<refentry id="sql-altercollation"> 7 <indexterm zone="sql-altercollation"> 8 <primary>ALTER COLLATION</primary> 9 </indexterm> 10 11 <refmeta> 12 <refentrytitle>ALTER COLLATION</refentrytitle> 13 <manvolnum>7</manvolnum> 14 <refmiscinfo>SQL - Language Statements</refmiscinfo> 15 </refmeta> 16 17 <refnamediv> 18 <refname>ALTER COLLATION</refname> 19 <refpurpose>change the definition of a collation</refpurpose> 20 </refnamediv> 21 22 <refsynopsisdiv> 23<synopsis> 24ALTER COLLATION <replaceable>name</replaceable> REFRESH VERSION 25 26ALTER COLLATION <replaceable>name</replaceable> RENAME TO <replaceable>new_name</replaceable> 27ALTER COLLATION <replaceable>name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_USER | SESSION_USER } 28ALTER COLLATION <replaceable>name</replaceable> SET SCHEMA <replaceable>new_schema</replaceable> 29</synopsis> 30 </refsynopsisdiv> 31 32 <refsect1> 33 <title>Description</title> 34 35 <para> 36 <command>ALTER COLLATION</command> changes the definition of a 37 collation. 38 </para> 39 40 <para> 41 You must own the collation to use <command>ALTER COLLATION</command>. 42 To alter the owner, you must also be a direct or indirect member of the new 43 owning role, and that role must have <literal>CREATE</literal> privilege on 44 the collation's schema. (These restrictions enforce that altering the 45 owner doesn't do anything you couldn't do by dropping and recreating the 46 collation. However, a superuser can alter ownership of any collation 47 anyway.) 48 </para> 49 </refsect1> 50 51 <refsect1> 52 <title>Parameters</title> 53 54 <variablelist> 55 <varlistentry> 56 <term><replaceable class="parameter">name</replaceable></term> 57 <listitem> 58 <para> 59 The name (optionally schema-qualified) of an existing collation. 60 </para> 61 </listitem> 62 </varlistentry> 63 64 <varlistentry> 65 <term><replaceable class="parameter">new_name</replaceable></term> 66 <listitem> 67 <para> 68 The new name of the collation. 69 </para> 70 </listitem> 71 </varlistentry> 72 73 <varlistentry> 74 <term><replaceable class="parameter">new_owner</replaceable></term> 75 <listitem> 76 <para> 77 The new owner of the collation. 78 </para> 79 </listitem> 80 </varlistentry> 81 82 <varlistentry> 83 <term><replaceable class="parameter">new_schema</replaceable></term> 84 <listitem> 85 <para> 86 The new schema for the collation. 87 </para> 88 </listitem> 89 </varlistentry> 90 91 <varlistentry> 92 <term><literal>REFRESH VERSION</literal></term> 93 <listitem> 94 <para> 95 Update the collation's version. 96 See <xref linkend="sql-altercollation-notes"/> below. 97 </para> 98 </listitem> 99 </varlistentry> 100 </variablelist> 101 </refsect1> 102 103 <refsect1 id="sql-altercollation-notes" xreflabel="Notes"> 104 <title>Notes</title> 105 106 <para> 107 When using collations provided by the ICU library, the ICU-specific version 108 of the collator is recorded in the system catalog when the collation object 109 is created. When the collation is used, the current version is 110 checked against the recorded version, and a warning is issued when there is 111 a mismatch, for example: 112<screen> 113WARNING: collation "xx-x-icu" has version mismatch 114DETAIL: The collation in the database was created using version 1.2.3.4, but the operating system provides version 2.3.4.5. 115HINT: Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION, or build PostgreSQL with the right library version. 116</screen> 117 A change in collation definitions can lead to corrupt indexes and other 118 problems because the database system relies on stored objects having a 119 certain sort order. Generally, this should be avoided, but it can happen 120 in legitimate circumstances, such as when 121 using <command>pg_upgrade</command> to upgrade to server binaries linked 122 with a newer version of ICU. When this happens, all objects depending on 123 the collation should be rebuilt, for example, 124 using <command>REINDEX</command>. When that is done, the collation version 125 can be refreshed using the command <literal>ALTER COLLATION ... REFRESH 126 VERSION</literal>. This will update the system catalog to record the 127 current collator version and will make the warning go away. Note that this 128 does not actually check whether all affected objects have been rebuilt 129 correctly. 130 </para> 131 <para> 132 When using collations provided by <literal>libc</literal> and 133 <productname>PostgreSQL</productname> was built with the GNU C library, the 134 C library's version is used as a collation version. Since collation 135 definitions typically change only with GNU C library releases, this provides 136 some defense against corruption, but it is not completely reliable. 137 </para> 138 <para> 139 Currently, there is no version tracking for the database default collation. 140 </para> 141 142 <para> 143 The following query can be used to identify all collations in the current 144 database that need to be refreshed and the objects that depend on them: 145<programlisting><![CDATA[ 146SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation", 147 pg_describe_object(classid, objid, objsubid) AS "Object" 148 FROM pg_depend d JOIN pg_collation c 149 ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid 150 WHERE c.collversion <> pg_collation_actual_version(c.oid) 151 ORDER BY 1, 2; 152]]></programlisting></para> 153 </refsect1> 154 155 <refsect1> 156 <title>Examples</title> 157 158 <para> 159 To rename the collation <literal>de_DE</literal> to 160 <literal>german</literal>: 161<programlisting> 162ALTER COLLATION "de_DE" RENAME TO german; 163</programlisting> 164 </para> 165 166 <para> 167 To change the owner of the collation <literal>en_US</literal> to 168 <literal>joe</literal>: 169<programlisting> 170ALTER COLLATION "en_US" OWNER TO joe; 171</programlisting></para> 172 </refsect1> 173 174 <refsect1> 175 <title>Compatibility</title> 176 177 <para> 178 There is no <command>ALTER COLLATION</command> statement in the SQL 179 standard. 180 </para> 181 </refsect1> 182 183 <refsect1> 184 <title>See Also</title> 185 186 <simplelist type="inline"> 187 <member><xref linkend="sql-createcollation"/></member> 188 <member><xref linkend="sql-dropcollation"/></member> 189 </simplelist> 190 </refsect1> 191</refentry> 192