On this page 
Warning:
CockroachDB v22.1 is no longer supported as of November 24, 2023. For more details, refer to the Release Support Policy.
The DROP TYPE statement drops a specified enumerated data type from the current database.
Note:
The DROP TYPE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Warning:
DROP TYPE now uses the declarative schema changer by default. Declarative schema changer statements and legacy schema changer statements operating on the same objects cannot exist within the same transaction. Either split the transaction into multiple transactions, or disable either the sql.defaults.use_declarative_schema_changer cluster setting or the use_declarative_schema_changer session variable.
Synopsis
Parameters
| Parameter | Description |
|---|---|
IF EXISTS |
Drop the type if it exists. If it does not exist, do not return an error. |
type_name_list |
A type name or a comma-separated list of type names to drop. |
Required privileges
The user must be the owner of the type.
Details
- You cannot drop a type or view that is in use by a table.
- You can only drop a user-defined type from the database that contains the type.
Example
Drop a single type
> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');
> SHOW ENUMS;
schema | name | value
---------+--------+-----------------------
public | status | open|closed|inactive
(1 row)
> CREATE TABLE accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
balance DECIMAL,
status status
);
> DROP TYPE status;
ERROR: cannot drop type "status" because other objects ([bank.public.accounts]) still depend on it
SQLSTATE: 2BP01
> DROP TABLE accounts;
> DROP TYPE status;
> SHOW ENUMS;
schema | name | value
---------+------+--------
(0 rows)
Drop multiple types
> CREATE TYPE weekday AS ENUM ('monday', 'tuesday', 'wednesday', 'thursday', 'friday');
> CREATE TYPE weekend AS ENUM ('sunday', 'saturday');
> SHOW ENUMS;
schema | name | value
---------+---------+-------------------------------------------
public | weekday | monday|tuesday|wednesday|thursday|friday
public | weekend | sunday|saturday
(2 rows)
> DROP TYPE weekday, weekend;
> SHOW ENUMS;
schema | name | value
---------+------+--------
(0 rows)
See also
Was this helpful?
