> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# ALTER TYPE

The `ALTER TYPE` [statement](/docs/stable/sql-statements.md) modifies a [user-defined data type](/docs/stable/create-type.md) in the current database.

> [!NOTE]
>
> To modify a [domain data type](/docs/stable/domain.md), use [`ALTER DOMAIN`](/docs/stable/alter-domain).

> [!NOTE]
>
> The `ALTER TYPE` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](/docs/stable/online-schema-changes.md).

## Synopsis

![alter\_type syntax diagram](https://mintcdn.com/cockroachlabs/VQ_w3c6MHYdiS7Tn/images/sql-diagrams/common/alter_type.svg?fit=max\&auto=format\&n=VQ_w3c6MHYdiS7Tn\&q=85\&s=c60db90ea65af1ed81008da34017c436)

## 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](/docs/stable/sql-name-resolution.md) of the user-defined type. |
| `OWNER TO` | Change the [role specification](/docs/stable/grant.md) for the user-defined type's owner. |

## Required privileges

* To [alter a type](/docs/stable/alter-type.md), 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](/docs/stable/security-reference/authorization.md#managing-privileges) 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 [`ALTER TYPE`](/docs/stable/alter-type) statement, you can only reference a user-defined type from the database that contains the type.
* You can only [cancel](/docs/stable/cancel-job.md) `ALTER TYPE` [schema change jobs](/docs/stable/online-schema-changes.md) 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](/docs/stable/cancel-job.md#known-limitations).

## Example

The following example uses a [user-defined type](/docs/stable/create-type.md).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TYPE status ADD VALUE 'pending';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TYPE status RENAME VALUE 'open' TO 'active';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TYPE status RENAME TO account_status;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TYPE account_status DROP VALUE 'inactive';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW ENUMS;
```

```
  schema |      name      |         values          | owner
---------+----------------+-------------------------+--------
  public | account_status | {active,closed,pending} | demo
(1 row)
```

## See also

* [`CREATE TYPE`](/docs/stable/create-type)
* [`ENUM`](/docs/stable/enum)
* [`SHOW ENUMS`](/docs/stable/show-enums)
* [`SHOW TYPES`](/docs/stable/show-types)
* [`DROP TYPE`](/docs/stable/drop-type)
* [Online Schema Changes](/docs/stable/online-schema-changes.md)