On this page 
Warning:
CockroachDB v20.2 is no longer supported as of May 10, 2022. For more details, refer to the Release Support Policy.
New in v20.2: The DROP TYPE statement drops a specified enumerated data type from the current database.
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?
