ALTER TYPE

On this page

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

Note:

You can only cancel ALTER TYPE schema change jobs that drop values. 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`	New in v21.1: Drop a specific value from the user-defined type's list of values. Note: `ALTER TYPE ... DROP VALUE` is disabled by default with the `enable_drop_enum_value` cluster setting set to `off`. To enable `ALTER TYPE ... DROP VALUE`, run `SET enable_drop_enum_value = on;`.
`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 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

You can only reference a user-defined type from the database that contains the type.
Expressions in views, default values, and computed columns will stop working if they reference an ENUM value dropped by an ALTER TYPE ... DROP VALUE statement. As a result, ALTER TYPE ... DROP VALUE is disabled by default with the enable_drop_enum_value cluster setting set to off. You can enable ALTER TYPE ... DROP VALUE by running SET enable_drop_enum_value = on;.

Example

> 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.

Note that expressions in views, default values, and computed columns will stop working if they reference a dropped ENUM value. As a result, ALTER TYPE ... DROP VALUE is disabled by default with the enable_drop_enum_value cluster setting set to off.

To enable ALTER TYPE ... DROP VALUE:

> SET enable_drop_enum_value = on;

Then, to drop a value from the type:

> ALTER TYPE account_status DROP VALUE 'inactive';

> SHOW ENUMS;

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

Pricing

Contact us

Sign In