DROP TYPE

DROP TYPE removes a custom data type. You cannot use it on default data types.

Syntax

DROP TYPE [IF EXISTS] <data_type_name> [RESTRICT|CASCADE];

Syntax element	Description
IF EXISTS	Optional. If specified, do not return an error if the named type doesn’t exist.
`<data_type_name>`	The name of the type to remove.
CASCADE	Optional. If specified, remove the type and its dependent objects, such as tables or other types.
RESTRICT	Optional. Don’t remove the type if any objects depend on it. (Default.)

Examples

Remove a type with no dependent objects

CREATE TYPE int4_map AS MAP (KEY TYPE = text, VALUE TYPE = int4);

SHOW TYPES;

    name
--------------
  int4_map
(1 row)

DROP TYPE int4_map;

SHOW TYPES;

  name
--------------
(0 rows)

Remove a type with dependent objects

By default, DROP TYPE will not remove a type with dependent objects. The CASCADE switch will remove both the specified type and all its dependent objects.

In the example below, the CASCADE switch removes int4_list, int4_list_list (which depends on int4_list), and the table t, which has a column of data type int4_list.

CREATE TYPE int4_list AS LIST (ELEMENT TYPE = int4);

CREATE TYPE int4_list_list AS LIST (ELEMENT TYPE = int4_list);

CREATE TABLE t (a int4_list);

SHOW TYPES;

      name
----------------
 int4_list
 int4_list_list
(2 rows)

DROP TYPE int4_list CASCADE;

SHOW TYPES;

SELECT * FROM t;

 name
------
(0 rows)
ERROR:  unknown catalog item 't'

Remove a type only if it has no dependent objects

You can use either of the following commands:

```
DROP TYPE int4_list;
```
```
DROP TYPE int4_list RESTRICT;
```

Do not issue an error if attempting to remove a nonexistent type

DROP TYPE IF EXISTS int4_list;

Privileges

The privileges required to execute this statement are:

Ownership of the dropped type.
USAGE privileges on the containing schema.