DROP TYPE

On this page Carat arrow pointing down

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