Postgresql 中文操作指南

ALTER TYPE

ALTER TYPE — 更改类型的定义

ALTER TYPE — change the definition of a type

Synopsis

ALTER TYPE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER TYPE name RENAME TO new_name
ALTER TYPE name SET SCHEMA new_schema
ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
ALTER TYPE name action [, ... ]
ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value
ALTER TYPE name SET ( property = value [, ... ] )

where action is one of:

    ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
    DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
    ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]

Description

ALTER TYPE 更改现有类型的定义。有几个子形式：

ALTER TYPE changes the definition of an existing type. There are several subforms:

  • OWNER

    • This form changes the owner of the type.

  • RENAME

    • This form changes the name of the type.

  • SET SCHEMA

    • This form moves the type into another schema.

  • RENAME ATTRIBUTE

    • This form is only usable with composite types. It changes the name of an individual attribute of the type.

  • ADD ATTRIBUTE

    • This form adds a new attribute to a composite type, using the same syntax as CREATE TYPE.

  • DROP ATTRIBUTE [ IF EXISTS ]

    • This form drops an attribute from a composite type. If IF EXISTS is specified and the attribute does not exist, no error is thrown. In this case a notice is issued instead.

  • ALTER ATTRIBUTE …​ SET DATA TYPE

    • This form changes the type of an attribute of a composite type.

  • ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]

    • This form adds a new value to an enum type. The new value’s place in the enum’s ordering can be specified as being BEFORE or AFTER one of the existing values. Otherwise, the new item is added at the end of the list of values.

    • If IF NOT EXISTS is specified, it is not an error if the type already contains the new value: a notice is issued but no other action is taken. Otherwise, an error will occur if the new value is already present.

  • RENAME VALUE

    • This form renames a value of an enum type. The value’s place in the enum’s ordering is not affected. An error will occur if the specified value is not present or the new name is already present.

  • SET ( _property = value [, …​ ] )_

    • This form is only applicable to base types. It allows adjustment of a subset of the base-type properties that can be set in CREATE TYPE. Specifically, these properties can be changed:

    • See CREATE TYPE for more details about these type properties. Note that where appropriate, a change in these properties for a base type will be propagated automatically to domains based on that type.

ADD ATTRIBUTE 、 DROP ATTRIBUTE 和 ALTER ATTRIBUTE 操作可以组合到一个列表中，从而平行应用多个更改。例如，可以添加多个属性和/或在单个命令中更改多个属性的类型。

The ADD ATTRIBUTE, DROP ATTRIBUTE, and ALTER ATTRIBUTE actions can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several attributes and/or alter the type of several attributes in a single command.

必须拥有才能使用 ALTER TYPE 类型。要更改类型的模式，还必须在新模式中拥有 CREATE 权限。要更改所有者，必须能够 SET ROLE 新所有者角色，并且该角色必须在新模式中具有 CREATE 权限。（这些限制强制要求更改所有者不会执行无法通过删除并重新创建类型来执行的操作。但是，超级用户可以随意更改任何类型的权限。）要添加属性或更改属性类型，还必须在新模式中具有 USAGE 权限。

You must own the type to use ALTER TYPE. To change the schema of a type, you must also have CREATE privilege on the new schema. 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 type’s schema. (These restrictions enforce that altering the owner doesn’t do anything you couldn’t do by dropping and recreating the type. However, a superuser can alter ownership of any type anyway.) To add an attribute or alter an attribute type, you must also have USAGE privilege on the attribute’s data type.

Parameters

  • name

    • The name (possibly schema-qualified) of an existing type to alter.

  • new_name

    • The new name for the type.

  • new_owner

    • The user name of the new owner of the type.

  • new_schema

    • The new schema for the type.

  • attribute_name

    • The name of the attribute to add, alter, or drop.

  • new_attribute_name

    • The new name of the attribute to be renamed.

  • data_type

    • The data type of the attribute to add, or the new type of the attribute to alter.

  • new_enum_value

    • The new value to be added to an enum type’s list of values, or the new name to be given to an existing value. Like all enum literals, it needs to be quoted.

  • neighbor_enum_value

    • The existing enum value that the new value should be added immediately before or after in the enum type’s sort ordering. Like all enum literals, it needs to be quoted.

  • existing_enum_value

    • The existing enum value that should be renamed. Like all enum literals, it needs to be quoted.

  • property

    • The name of a base-type property to be modified; see above for possible values.

  • CASCADE

    • Automatically propagate the operation to typed tables of the type being altered, and their descendants.

  • RESTRICT

    • Refuse the operation if the type being altered is the type of a typed table. This is the default.

Notes

如果在事务块内执行 ALTER TYPE …​ ADD VALUE （为枚举类型添加新值的形式），那么在提交事务之前无法使用新值。

If ALTER TYPE …​ ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committed.

涉及添加的枚举值的比较有时会比仅涉及枚举类型原始成员的比较慢。通常只有在使用 BEFORE 或 AFTER 将新值的排序位置设置为不位于列表末尾时才会发生这种情况。但是，即使新值添加到末尾，有时也会发生这种情况（如果自枚举类型最初创建以来，OID 计数器“环绕”）。这种减速通常微不足道；但是，如果很重要，可以通过删除和重新创建枚举类型或转储和恢复数据库来重新获得最佳性能。

Comparisons involving an added enum value will sometimes be slower than comparisons involving only original members of the enum type. This will usually only occur if BEFORE or AFTER is used to set the new value’s sort position somewhere other than at the end of the list. However, sometimes it will happen even though the new value is added at the end (this occurs if the OID counter “wrapped around” since the original creation of the enum type). The slowdown is usually insignificant; but if it matters, optimal performance can be regained by dropping and recreating the enum type, or by dumping and restoring the database.

Examples

重命名数据类型：

To rename a data type:

ALTER TYPE electronic_mail RENAME TO email;

将类型 email 的所有者更改为 joe ：

To change the owner of the type email to joe:

ALTER TYPE email OWNER TO joe;

将类型 email 的模式更改为 customers ：

To change the schema of the type email to customers:

ALTER TYPE email SET SCHEMA customers;

向复合类型添加新属性：

To add a new attribute to a composite type:

ALTER TYPE compfoo ADD ATTRIBUTE f3 int;

在特定排序位置向枚举类型添加新值：

To add a new value to an enum type in a particular sort position:

ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';

重命名枚举值：

To rename an enum value:

ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';

为现有基本类型创建二进制 I/O 函数：

To create binary I/O functions for an existing base type:

CREATE FUNCTION mytypesend(mytype) RETURNS bytea ...;
CREATE FUNCTION mytyperecv(internal, oid, integer) RETURNS mytype ...;
ALTER TYPE mytype SET (
    SEND = mytypesend,
    RECEIVE = mytyperecv
);

Compatibility

添加和删除属性的变体是 SQL 标准的一部分；其他变体是 PostgreSQL 扩展。

The variants to add and drop attributes are part of the SQL standard; the other variants are PostgreSQL extensions.

See Also

CREATE TYPE ， DROP TYPE

CREATE TYPE, DROP TYPE