ALTER TYPE

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

ALTER TYPE type_name ADD VALUE IF NOT EXISTS value BEFORE AFTER DROP VALUE value RENAME VALUE value TO value TO name SET SCHEMA schema_name OWNER TO role_spec

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

icon/buttons/copy
> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');
icon/buttons/copy
> 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:

icon/buttons/copy
> ALTER TYPE status ADD VALUE 'pending';
icon/buttons/copy
> 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:

icon/buttons/copy
> ALTER TYPE status RENAME VALUE 'open' TO 'active';
icon/buttons/copy
> 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:

icon/buttons/copy
> ALTER TYPE status RENAME TO account_status;
icon/buttons/copy
> 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:

icon/buttons/copy
> SET enable_drop_enum_value = on;

Then, to drop a value from the type:

icon/buttons/copy
> ALTER TYPE account_status DROP VALUE 'inactive';
icon/buttons/copy
> SHOW ENUMS;
  schema |      name      |         values          | owner
---------+----------------+-------------------------+--------
  public | account_status | {active,closed,pending} | demo
(1 row)

See also

YesYes NoNo