ALTER TYPE

On this page

The ALTER TYPE statement modifies a user-defined, enumerated 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

• 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)

See also

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

Was this helpful?