Postgresql 中文操作指南

ALTER COLLATION

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_ROLE | CURRENT_USER | SESSION_USER }
ALTER COLLATION name SET SCHEMA new_schema

Description

ALTER COLLATION 更改校对的定义。

ALTER COLLATION changes the definition of a collation.

您必须拥有校对才能使用 ALTER COLLATION 。要更改拥有者，您必须能够 SET ROLE 到新的拥有者角色，并且该角色必须具有对校对架构的 CREATE 权限。（这些限制确保更改拥有者不会执行您无法通过删除和重新创建校对来完成的任何操作。但是，超级用户无论如何都可以更改任何校对的所有权。）

You must own the collation to use ALTER COLLATION. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the collation’s schema. (These restrictions enforce that altering the owner doesn’t do anything you couldn’t do by dropping and recreating the collation. However, a superuser can alter ownership of any collation anyway.)

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

    • Update the collation’s version. See Notes below.

Notes

创建校对对象时，特定于提供程序的校对版本将记录在系统目录中。当使用校对时，会将当前版本与记录版本进行核对，当两者不匹配时将发出警告，例如：

When a collation object is created, the provider-specific version of the collation is recorded in the system catalog. When the collation is used, the current version is checked against the recorded version, and a warning is issued when there is a mismatch, for 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.

校对定义的更改可能导致索引损坏及其他问题，因为数据库系统依赖于存储的对象具有特定的排序顺序。通常应避免出现这种情况，但它可能出于合理情况而发生，例如在将操作系统升级到新主要版本或使用 pg_upgrade 升级到链接了新版本 ICU 的服务器二进制文件时。发生这种情况时，应重建所有依赖于校对的对象，例如使用 REINDEX 。完成操作后，可以使用命令 ALTER COLLATION …​ REFRESH VERSION 刷新校对版本。这将更新系统目录以记录当前校对版本，并且将消除警告。请注意，这实际上并没有检查所有受影响的对象是否已正确重建。

A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain 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 pg_upgrade 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 REINDEX. When that is done, the collation version can be refreshed using the command ALTER COLLATION …​ REFRESH VERSION. 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.

在使用 libc 提供的校对时，将记录版本信息到使用 GNU C 库（大多数 Linux 系统）、FreeBSD 和 Windows 的系统上。在使用由 ICU 提供的校对时，版本信息由 ICU 库提供，并且在所有平台上均可用。

When using collations provided by libc, 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.

Note

在为校对使用 GNU C 库时，C 库的版本用作校对版本的代理。许多 Linux 发行版仅在升级 C 库时才更改校对定义，但此方法并不完善，因为维护人员可以自由地将较新的校对定义移植到较旧的 C 库版本中。

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.

在为校对使用 Windows 时，版本信息仅适用于使用 BCP 47 语言标签（如 en-US ）定义的校对。

When using Windows for collations, version information is only available for collations defined with BCP 47 language tags such as en-US.

对于数据库默认校对，有相应的命令 ALTER DATABASE …​ REFRESH COLLATION VERSION 。

For the database default collation, there is an analogous command ALTER DATABASE …​ REFRESH COLLATION VERSION.

可以使用以下查询识别当前数据库中需要刷新的所有校对以及依赖于这些校对的对象：

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:

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;

Examples

将校对 de_DE 重命名为 german ：

To rename the collation de_DE to german:

ALTER COLLATION "de_DE" RENAME TO german;

将校对 en_US 的拥有者更改为 joe ：

To change the owner of the collation en_US to joe:

ALTER COLLATION "en_US" OWNER TO joe;

Compatibility

SQL 标准中没有 ALTER COLLATION 语句。

There is no ALTER COLLATION statement in the SQL standard.

See Also

CREATE COLLATION ， DROP COLLATION

CREATE COLLATION, DROP COLLATION