ALTER TYPE

On this page

The ALTER TYPE statement modifies a user-defined data type in the current database.

Synopsis

Parameters

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 CREATE privilege 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 statement, you can only reference a user-defined type from the database that contains the type.
• 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.

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

• CREATE TYPE
• ENUM
• SHOW ENUMS
• SHOW TYPES
• DROP TYPE
• Online Schema Changes

ALTER TYPE ... RENAME TO

The ALTER TYPE ... RENAME TO clause renames an existing enum type and its associated implicit array type.

Synopsis

ALTER TYPE type_name RENAME TO new_type_name

Parameters

Required privileges

The user must have the CREATE privilege on the schema containing the type.

Details

• Only enum types can be renamed using this syntax. Attempting to rename composite types or domain types will result in an error stating that ALTER TYPE on non-enum user-defined types is not supported.
• The statement automatically renames both the enum type and its associated implicit array type.
• Renaming a type to its current name results in an error, unlike table renames which are treated as no-ops.
• The new name must not conflict with any existing objects (tables, types, domains) in the same schema.
• Multiple rename operations can be performed within a single transaction.
• The implicit array type is automatically renamed by prepending underscores to find a non-conflicting name.

Examples

Basic enum type rename

-- Create an enum type
CREATE TYPE status AS ENUM ('pending', 'approved', 'rejected');

-- Rename the enum type
ALTER TYPE status RENAME TO order_status;

-- Use the renamed type
SELECT 'approved'::order_status;

Multiple renames in a transaction

BEGIN;
ALTER TYPE order_status RENAME TO temp_status;
ALTER TYPE temp_status RENAME TO final_status;
SELECT 'pending'::final_status;
COMMIT;

Error conditions

The statement will fail if:

• The target type is not an enum type
• The new name already exists as a table, type, or domain in the same schema
• You attempt to rename a type to its current name
• An object with the target name is currently being dropped
• You lack the required privileges

-- This will fail - renaming to the same name
ALTER TYPE order_status RENAME TO order_status;

ERROR: type "defaultdb.public.order_status" already exists

-- This will fail if a table named 'users' exists
ALTER TYPE order_status RENAME TO users;

ERROR: relation "defaultdb.public.users" already exists

-- This will fail if a domain named 'user_id' exists
ALTER TYPE order_status RENAME TO user_id;

ERROR: type "defaultdb.public.user_id" already exists

See also

• CREATE TYPE
• ALTER TYPE
• DROP TYPE
• Enum data types

Integration notes: - This content should be added to the existing ALTER TYPE reference page as a new section - The main ALTER TYPE synopsis should be updated to include the RENAME TO clause - This functionality requires cluster version 26.3 or later (gated by isV263Active)