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
CREATE
privilege on the schema and theDROP
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 anALTER TYPE ... DROP VALUE
statement. As a result,ALTER TYPE ... DROP VALUE
is disabled by default with theenable_drop_enum_value
cluster setting set tooff
. You can enableALTER TYPE ... DROP VALUE
by 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)