ALTER TYPE

The ALTER TYPE modifies a in the current database.

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

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 of the user-defined type.
OWNER TO	Change the for the user-defined type’s owner.

Required privileges

• To , the user must be the owner of the type.
• To set the schema of a user-defined type, the user must have the CREATE 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.

Known limitations

• When running the statement, you can only reference a user-defined type from the database that contains the type.
• You can only ALTER TYPE 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 .

Example

The following example uses a .

> 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