Home Explore Blog CI



postgresql

3rd chunk of `doc/src/sgml/ref/alter_collation.sgml`
48647e5d2b9e8bbe2ebc7c636fd5febe1339ba6b2ee045da0000000100000c8b
 sort order.  Generally, this should be avoided, but it can happen
   in legitimate circumstances, such as when upgrading the operating system
   to a new major version or when
   using <command>pg_upgrade</command> to upgrade to server binaries linked
   with a newer version of ICU.  When this happens, all objects depending on
   the collation should be rebuilt, for example,
   using <command>REINDEX</command>.  When that is done, the collation version
   can be refreshed using the command <literal>ALTER COLLATION ... REFRESH
   VERSION</literal>.  This will update the system catalog to record the
   current collation version and will make the warning go away.  Note that this
   does not actually check whether all affected objects have been rebuilt
   correctly.
  </para>
  <para>
   When using collations provided by <literal>libc</literal>, version
   information is recorded on systems using the GNU C library (most Linux
   systems), FreeBSD and Windows.  When using collations provided by ICU, the
   version information is provided by the ICU library and is available on all
   platforms.
  </para>
  <note>
   <para>
    When using the GNU C library for collations, the C library's version
    is used as a proxy for the collation version.  Many Linux distributions
    change collation definitions only when upgrading the C library, but this
    approach is imperfect as maintainers are free to back-port newer
    collation definitions to older C library releases.
   </para>
   <para>
    When using Windows for collations, version information is only available
    for collations defined with BCP 47 language tags such as
    <literal>en-US</literal>.
   </para>
  </note>
  <para>
   For the database default collation, there is an analogous command
   <literal>ALTER DATABASE ... REFRESH COLLATION VERSION</literal>.
  </para>

  <para>
   The following query can be used to identify all collations in the current
   database that need to be refreshed and the objects that depend on them:
<programlisting><![CDATA[
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
       pg_describe_object(classid, objid, objsubid) AS "Object"
  FROM pg_depend d JOIN pg_collation c
       ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
  WHERE c.collversion <> pg_collation_actual_version(c.oid)
  ORDER BY 1, 2;
]]></programlisting></para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   To rename the collation <literal>de_DE</literal> to
   <literal>german</literal>:
<programlisting>
ALTER COLLATION "de_DE" RENAME TO german;
</programlisting>
  </para>

  <para>
   To change the owner of the collation <literal>en_US</literal> to
   <literal>joe</literal>:
<programlisting>
ALTER COLLATION "en_US" OWNER TO joe;
</programlisting></para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   There is no <command>ALTER COLLATION</command> statement in the SQL
   standard.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-createcollation"/></member>
   <member><xref linkend="sql-dropcollation"/></member>
  </simplelist>
 </refsect1>
</refentry>

Title: ALTER COLLATION: Versioning, Refreshing, and Examples
Summary
This section details versioning of collations and refreshing them after system upgrades or when using `pg_upgrade`. It notes how collation version information is handled with `libc` (GNU C library, FreeBSD, Windows) and ICU, including caveats about GNU C library's versioning and Windows collation version availability. It also mentions `ALTER DATABASE ... REFRESH COLLATION VERSION` for the database default collation. A SQL query is provided to identify collations needing refresh and their dependencies. Examples show how to rename a collation and change its owner. Finally, it notes the absence of `ALTER COLLATION` in the SQL standard and refers to related commands.