The ALTER TYPE statement modifies a user-defined, enumerated data type in the current database.
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
CREATEprivilege on the schema and theDROPprivilege 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
CREATEprivilege 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
ENUMvalue dropped by anALTER TYPE ... DROP VALUEstatement. As a result,ALTER TYPE ... DROP VALUEis disabled by default with theenable_drop_enum_valuecluster setting set tooff. You can enableALTER TYPE ... DROP VALUEby runningSET 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
Was this helpful?
