ALTER COLLATION
ALTER COLLATION — Change the Definition of a Collation
Synopsis
ALTER COLLATION name REFRESH VERSION
ALTER COLLATION name RENAME TO new_name
ALTER COLLATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER COLLATION name SET SCHEMA new_schema
Description
ALTER COLLATION changes the definition of a collation.
You must own the collation to use ALTER COLLATION. To change the owner, you must be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the collation's schema (these restrictions enforce that the owner cannot do anything you couldn't do by dropping and recreating the collation. However, a superuser can change ownership of any collation).
Parameters
name
The name (optionally schema-qualified) of an existing collation.
new_name
The new name of the collation.
new_owner
The new owner of the collation.
new_schema
The new schema for the collation.
REFRESH VERSION
Updates the version of the collation.
:::tip Note
When using collations provided by the ICU library, the specific ICU version of the collation is recorded in the system catalog when the collation object is created. When the collation is used, the current version is checked against the recorded version, and a warning is issued if there is a mismatch.
:::
Example
WARNING: collation "xx-x-icu" has version mismatch
DETAIL: The collation in the database was created using version 1.2.3.4,
but the operating system provides version 2.3.4.5.
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.
Changes to the collation definition can cause index corruption and other problems because the database system relies on stored objects having a specific sort order. Typically, this should be avoided, but it can happen legitimately, for example when using pg_upgrade to upgrade to a server binary linked with a newer version of ICU. When this occurs, all objects dependent on the collation should be rebuilt, for example using REINDEX. Once done, the command ALTER COLLATION ... REFRESH VERSION can be used to refresh the collation version. This updates the system catalog to record the current collation version and makes the warning disappear. Note that this does not actually verify that all affected objects have been properly rebuilt.
Examples
# To rename the collation de_DE to german:
ALTER COLLATION "de_DE" RENAME TO german;
# To change the owner of the collation en_US to joe:
ALTER COLLATION "en_US" OWNER TO joe;