DROP TYPE

On this page Carat arrow pointing down
Warning:
Cockroach Labs will stop providing Assistance Support for v23.1 on November 15, 2024. Prior to that date, upgrade to a more recent version to continue receiving support. 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.

Examples

Drop a single type

icon/buttons/copy
> CREATE TYPE IF NOT EXISTS 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 IF NOT EXISTS accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        balance DECIMAL,
        status status
);
icon/buttons/copy
-- sqlchecker: ignore
> 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