New in v20.2: The DROP TYPE statement drops a specified enumerated data type from the current database.

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

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

Drop multiple types

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

See also



YesYes NoNo