Skip to content

Latest commit

 

History

History
90 lines (71 loc) · 2.61 KB

alter-type.md

File metadata and controls

90 lines (71 loc) · 2.61 KB
title summary toc
ALTER TYPE
The ALTER TYPE statement modifies a user-defined data type in a database.
true

New in v20.2: The ALTER TYPE statement modifies a user-defined, enumerated data type in the current database.

{{site.data.alerts.callout_info}} You can only reference a user-defined type from the database that contains the type. {{site.data.alerts.end}}

Synopsis

{% include {{ page.version.version }}/sql/diagrams/alter_type.html %}

Parameters

Parameter Description
type_name The name of the user-defined type.
ADD VALUE value Add a constant value to the user-defined type's list of values. You can optionally specify BEFORE value or AFTER value to add the value in sort order relative to an existing value.
RENAME TO name Rename the user-defined type.
RENAME VALUE value TO value Rename a constant value in the user-defined type's list of values.
SET SCHEMA Set the schema of the user-defined type.
OWNER TO Change the role specification for the user-defined type's owner.

Required privileges

  • To alter a type, the user must be the owner of the type.
  • To set the schema of a user-defined type, the user must have the CREATE privilege on the schema and the DROP privilege on the type.
  • To alter the owner of a user-defined type:
    • The user executing the command must be a member of the new owner role.
    • The new owner role must have the CREATE privilege on the schema the type belongs to.

Example

{% include copy-clipboard.html %}

> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');

{% include copy-clipboard.html %}

> SHOW ENUMS;
  schema |  name  |        value
---------+--------+-----------------------
  public | status | open|closed|inactive
(1 row)

{% include copy-clipboard.html %}

> ALTER TYPE status ADD VALUE 'pending';

{% include copy-clipboard.html %}

> ALTER TYPE status RENAME VALUE 'open' TO 'active';

{% include copy-clipboard.html %}

> ALTER TYPE status RENAME TO account_status;

{% include copy-clipboard.html %}

> SHOW ENUMS;
  schema |      name      |             value
---------+----------------+---------------------------------
  public | account_status | active|closed|inactive|pending
(1 row)

See also