The ALTER TYPE statement modifies a user-defined data type in the current database.
The ALTER TYPE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
You can only cancel ALTER TYPE schema 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 other ALTER TYPE schema change jobs are non-cancellable.
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
- You can only reference a user-defined type from the database that contains the type.
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?
