DROP TYPE

The DROP TYPE drops a specified from the current database.

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

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.

Examples

Drop a single type

> CREATE TYPE IF NOT EXISTS status AS ENUM ('open', 'closed', 'inactive');

> SHOW ENUMS;

  schema |  name  |        value
---------+--------+-----------------------
  public | status | open|closed|inactive
(1 row)

> CREATE TABLE IF NOT EXISTS accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        balance DECIMAL,
        status status
);

-- sqlchecker: ignore
> 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