On this page 
Warning:
CockroachDB v20.2 is no longer supported. For more details, see the Release Support Policy.
New in v20.2: The ALTER TYPE statement modifies a user-defined, enumerated data type in the current database.
Note:
You can only reference a user-defined type from the database that contains the type.
Synopsis
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
CREATEprivilege on the schema and theDROPprivilege 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
CREATEprivilege on the schema the type belongs to.
Example
> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');
> SHOW ENUMS;
schema | name | value
---------+--------+-----------------------
public | status | open|closed|inactive
(1 row)
> ALTER TYPE status ADD VALUE 'pending';
> ALTER TYPE status RENAME VALUE 'open' TO 'active';
> ALTER TYPE status RENAME TO account_status;
> SHOW ENUMS;
schema | name | value
---------+----------------+---------------------------------
public | account_status | active|closed|inactive|pending
(1 row)
See also
Was this helpful?
