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

# ALTER TYPE

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `ALTER TYPE` <InternalLink path="sql-statements">statement</InternalLink> modifies a <InternalLink path="create-type">user-defined data type</InternalLink> in the current database.

The `ALTER TYPE` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>.

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/2sY29RlbaeZjvP07/images/sql-diagrams/v26.1/alter_type.svg?fit=max&auto=format&n=2sY29RlbaeZjvP07&q=85&s=071f3e9b3f0767fc48bddddf9217327b" alt="alter_type syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="839" height="399" data-path="images/sql-diagrams/v26.1/alter_type.svg" />

## 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 <InternalLink path="sql-name-resolution">the schema</InternalLink> of the user-defined type.                                                                                         |
| `OWNER TO`                    | Change the <InternalLink path="grant">role specification</InternalLink> for the user-defined type's owner.                                                                               |

## Required privileges

* To <InternalLink path="alter-type">alter a type</InternalLink>, the user must be the owner of the type.
* To set the schema of a user-defined type, the user must have the `CREATE` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> 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 <InternalLink path="alter-type">`ALTER TYPE`</InternalLink> statement, you can only reference a user-defined type from the database that contains the type.
* You can only <InternalLink path="cancel-job">cancel</InternalLink> `ALTER TYPE` <InternalLink path="online-schema-changes">schema change jobs</InternalLink> 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 <InternalLink path="cancel-job#known-limitations">non-cancellable</InternalLink>.

## Example

The following example uses a <InternalLink path="create-type">user-defined type</InternalLink>.

```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;
```

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

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

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

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  schema |      name      |         values          | owner
---------+----------------+-------------------------+--------
  public | account_status | {active,closed,pending} | demo
(1 row)
```

## See also

* <InternalLink path="create-type">`CREATE TYPE`</InternalLink>
* <InternalLink path="enum">`ENUM`</InternalLink>
* <InternalLink path="show-enums">`SHOW ENUMS`</InternalLink>
* <InternalLink path="show-types">`SHOW TYPES`</InternalLink>
* <InternalLink path="drop-type">`DROP TYPE`</InternalLink>
* <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>