On this page 
The ALTER TYPE statement modifies a user-defined data type in the current database.
Note:
The ALTER TYPE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
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. |
DROP VALUE value |
Drop a specific value from the user-defined type's list of values. |
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.
Known limitations
- When running the
ALTER TYPEstatement, you can only reference a user-defined type from the database that contains the type. - You can only cancel
ALTER TYPEschema change jobs that drop values. This is because when you drop a value, CockroachDB searches through every row that could contain the type's value, which could take a long time. All otherALTER TYPEschema change jobs are non-cancellable.
Example
The following example uses a user-defined type.
> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');
> SHOW ENUMS;
schema | name | values | owner
---------+--------+------------------------+--------
public | status | {open,closed,inactive} | demo
(1 row)
Add a value to a user-defined type
To add a value to the status type, use an ADD VALUE clause:
> ALTER TYPE status ADD VALUE 'pending';
> SHOW ENUMS;
schema | name | values | owner
---------+--------+--------------------------------+--------
public | status | {open,closed,inactive,pending} | demo
(1 row)
Rename a value in a user-defined type
To rename a value in the status type, use a RENAME VALUE clause:
> ALTER TYPE status RENAME VALUE 'open' TO 'active';
> SHOW ENUMS;
schema | name | values | owner
---------+--------+----------------------------------+--------
public | status | {active,closed,inactive,pending} | demo
(1 row)
Rename a user-defined type
To rename the status type, use a RENAME TO clause:
> ALTER TYPE status RENAME TO account_status;
> SHOW ENUMS;
schema | name | values | owner
---------+----------------+----------------------------------+--------
public | account_status | {active,closed,inactive,pending} | demo
(1 row)
Drop a value in a user-defined type
To drop a value from the account_status type, use a DROP VALUE clause:
> ALTER TYPE account_status DROP VALUE 'inactive';
> SHOW ENUMS;
schema | name | values | owner
---------+----------------+-------------------------+--------
public | account_status | {active,closed,pending} | demo
(1 row)
See also
Was this helpful?
