doc/src/sgml/ref/alter_collation.sgml

   1 <!--
   2 doc/src/sgml/ref/alter_collation.sgml
   3 PostgreSQL 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>
  24 ALTER COLLATION <replaceable>name</replaceable> REFRESH VERSION
  25
  26 ALTER COLLATION <replaceable>name</replaceable> RENAME TO <replaceable>new_name</replaceable>
  27 ALTER COLLATION <replaceable>name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
  28 ALTER 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 be able to <literal>SET ROLE</literal> to the
  43    new owning role, and that role must have <literal>CREATE</literal>
  44    privilege on the collation's schema.
  45    (These restrictions enforce that altering the
  46    owner doesn't do anything you couldn't do by dropping and recreating the
  47    collation. However, a superuser can alter ownership of any collation
  48    anyway.)
  49   </para>
  50  </refsect1>
  51
  52  <refsect1>
  53   <title>Parameters</title>
  54
  55   <variablelist>
  56    <varlistentry>
  57     <term><replaceable class="parameter">name</replaceable></term>
  58     <listitem>
  59      <para>
  60       The name (optionally schema-qualified) of an existing collation.
  61      </para>
  62     </listitem>
  63    </varlistentry>
  64
  65    <varlistentry>
  66     <term><replaceable class="parameter">new_name</replaceable></term>
  67     <listitem>
  68      <para>
  69       The new name of the collation.
  70      </para>
  71     </listitem>
  72    </varlistentry>
  73
  74    <varlistentry>
  75     <term><replaceable class="parameter">new_owner</replaceable></term>
  76     <listitem>
  77      <para>
  78       The new owner of the collation.
  79      </para>
  80     </listitem>
  81    </varlistentry>
  82
  83    <varlistentry>
  84     <term><replaceable class="parameter">new_schema</replaceable></term>
  85     <listitem>
  86      <para>
  87       The new schema for the collation.
  88      </para>
  89     </listitem>
  90    </varlistentry>
  91
  92    <varlistentry>
  93     <term><literal>REFRESH VERSION</literal></term>
  94     <listitem>
  95      <para>
  96       Update the collation's version.
  97       See <xref linkend="sql-altercollation-notes"/> below.
  98      </para>
  99     </listitem>
 100    </varlistentry>
 101   </variablelist>
 102  </refsect1>
 103
 104  <refsect1 id="sql-altercollation-notes" xreflabel="Notes">
 105   <title>Notes</title>
 106
 107   <para>
 108    When a collation object is created, the provider-specific version of the
 109    collation is recorded in the system catalog.  When the collation is used,
 110    the current version is
 111    checked against the recorded version, and a warning is issued when there is
 112    a mismatch, for example:
 113 <screen>
 114 WARNING:  collation "xx-x-icu" has version mismatch
 115 DETAIL:  The collation in the database was created using version 1.2.3.4, but the operating system provides version 2.3.4.5.
 116 HINT:  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.
 117 </screen>
 118    A change in collation definitions can lead to corrupt indexes and other
 119    problems because the database system relies on stored objects having a
 120    certain sort order.  Generally, this should be avoided, but it can happen
 121    in legitimate circumstances, such as when upgrading the operating system
 122    to a new major version or when
 123    using <command>pg_upgrade</command> to upgrade to server binaries linked
 124    with a newer version of ICU.  When this happens, all objects depending on
 125    the collation should be rebuilt, for example,
 126    using <command>REINDEX</command>.  When that is done, the collation version
 127    can be refreshed using the command <literal>ALTER COLLATION ... REFRESH
 128    VERSION</literal>.  This will update the system catalog to record the
 129    current collation version and will make the warning go away.  Note that this
 130    does not actually check whether all affected objects have been rebuilt
 131    correctly.
 132   </para>
 133   <para>
 134    When using collations provided by <literal>libc</literal>, version
 135    information is recorded on systems using the GNU C library (most Linux
 136    systems), FreeBSD and Windows.  When using collations provided by ICU, the
 137    version information is provided by the ICU library and is available on all
 138    platforms.
 139   </para>
 140   <note>
 141    <para>
 142     When using the GNU C library for collations, the C library's version
 143     is used as a proxy for the collation version.  Many Linux distributions
 144     change collation definitions only when upgrading the C library, but this
 145     approach is imperfect as maintainers are free to back-port newer
 146     collation definitions to older C library releases.
 147    </para>
 148    <para>
 149     When using Windows for collations, version information is only available
 150     for collations defined with BCP 47 language tags such as
 151     <literal>en-US</literal>.
 152    </para>
 153   </note>
 154   <para>
 155    For the database default collation, there is an analogous command
 156    <literal>ALTER DATABASE ... REFRESH COLLATION VERSION</literal>.
 157   </para>
 158
 159   <para>
 160    The following query can be used to identify all collations in the current
 161    database that need to be refreshed and the objects that depend on them:
 162 <programlisting><![CDATA[
 163 SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
 164        pg_describe_object(classid, objid, objsubid) AS "Object"
 165   FROM pg_depend d JOIN pg_collation c
 166        ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
 167   WHERE c.collversion <> pg_collation_actual_version(c.oid)
 168   ORDER BY 1, 2;
 169 ]]></programlisting></para>
 170  </refsect1>
 171
 172  <refsect1>
 173   <title>Examples</title>
 174
 175   <para>
 176    To rename the collation <literal>de_DE</literal> to
 177    <literal>german</literal>:
 178 <programlisting>
 179 ALTER COLLATION "de_DE" RENAME TO german;
 180 </programlisting>
 181   </para>
 182
 183   <para>
 184    To change the owner of the collation <literal>en_US</literal> to
 185    <literal>joe</literal>:
 186 <programlisting>
 187 ALTER COLLATION "en_US" OWNER TO joe;
 188 </programlisting></para>
 189  </refsect1>
 190
 191  <refsect1>
 192   <title>Compatibility</title>
 193
 194   <para>
 195    There is no <command>ALTER COLLATION</command> statement in the SQL
 196    standard.
 197   </para>
 198  </refsect1>
 199
 200  <refsect1>
 201   <title>See Also</title>
 202
 203   <simplelist type="inline">
 204    <member><xref linkend="sql-createcollation"/></member>
 205    <member><xref linkend="sql-dropcollation"/></member>
 206   </simplelist>
 207  </refsect1>
 208 </refentry>