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