DROP TYPE

On this page Carat arrow pointing down
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

DROP TYPE IF EXISTS type_name_list

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

icon/buttons/copy
> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');
icon/buttons/copy
> SHOW ENUMS;
  schema |  name  |        value
---------+--------+-----------------------
  public | status | open|closed|inactive
(1 row)
icon/buttons/copy
> CREATE TABLE accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        balance DECIMAL,
        status status
);
icon/buttons/copy
> DROP TYPE status;
ERROR: cannot drop type "status" because other objects ([bank.public.accounts]) still depend on it
SQLSTATE: 2BP01
icon/buttons/copy
> DROP TABLE accounts;
icon/buttons/copy
> DROP TYPE status;
icon/buttons/copy
> SHOW ENUMS;
  schema | name | value
---------+------+--------
(0 rows)

Drop multiple types

icon/buttons/copy
> CREATE TYPE weekday AS ENUM ('monday', 'tuesday', 'wednesday', 'thursday', 'friday');
icon/buttons/copy
> CREATE TYPE weekend AS ENUM ('sunday', 'saturday');
icon/buttons/copy
> SHOW ENUMS;
  schema |  name   |                  value
---------+---------+-------------------------------------------
  public | weekday | monday|tuesday|wednesday|thursday|friday
  public | weekend | sunday|saturday
(2 rows)
icon/buttons/copy
> DROP TYPE weekday, weekend;
icon/buttons/copy
> SHOW ENUMS;
  schema | name | value
---------+------+--------
(0 rows)

See also


Yes No
On this page

Yes No