PostgreSQL DROP TYPE Statement

Summary: In this tutorial, you will learn how to use the PostgreSQL DROP TYPE statement to remove a user-defined type from the database.

Introduction to the PostgreSQL DROP TYPE statement #

The DROP TYPE statement allows you to remove one or more user-defined data types from a database.

Here’s the syntax of the DROP TYPE statement:

DROP TYPE [IF EXISTS] type_name [CASCADE | RESTRICT];

In this syntax:

First, specify the name of the user-defined type you want to remove after the DROP TYPE keywords.
Second, use IF EXISTS to remove the data type only if it exists. This prevents errors if the type does not exist, making your command more robust.
Third, use CASCADE to drop objects that depend on the type, such as table columns and functions, and in turn, all objects that depend on those objects. The DROP TYPE statement uses the RESTRICT option by default, which rejects dropping the type if any objects depend on it.

If you want to drop more than one type at the same time, you can list them in the DROP TYPE statement:

DROP TYPE type_name1, type_name2, ...;

Basic DROP TYPE statement example #

First, create a new type called contact:

CREATE TYPE contact AS ( 
    phone TEXT, 
    email TEXT 
);

Try it

Second, drop the contact type using the DROP TYPE statement:

DROP TYPE IF EXISTS contact;

Try it

Dropping user-defined types with dependent objects #

First, create a new composite type called address that includes city, street, zip_code, state, and country:

CREATE TYPE address AS (
  city VARCHAR,
  street VARCHAR,
  zip_code VARCHAR,
  state VARCHAR,
  country VARCHAR
);

Try it

Second, create a table called customers to store customer information. The customers table uses the address type:

CREATE TABLE customers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  name VARCHAR NOT NULL,
  email VARCHAR NOT NULL,
  billing_address address,
  shipping_address address
);

Try it

Third, attempt to drop the address type:

DROP TYPE address;

Try it

PostgreSQL issues the following error:

ERROR:  cannot drop type address because other objects depend on it
DETAIL:  column shipping_address of table customers depends on type address
column billing_address of table customers depends on type address
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The error shows that the shipping_address and billing_address columns of the customers table use the address type; therefore, you cannot drop it with RESTRICT (the default). You must use DROP TYPE ... CASCADE to drop dependent objects as well.

If these columns have data and you want to keep it, you need to back up the table, change the type of these columns (e.g., to TEXT), and then drop the type without using the CASCADE option.

Finally, drop the type address with the CASCADE option:

DROP TYPE address CASCADE;

Try it

Output:

NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to column shipping_address of table customers
drop cascades to column billing_address of table customers

The output shows that the statement dropped the shipping_address and billing_address columns from the customers table.

Summary #

Use the DROP TYPE statement to remove a user-defined type from a database.
Use the IF EXISTS option to avoid errors if the type does not exist.
Carefully review the dependent objects before using the CASCADE option, as it will delete those objects as well.

Quiz #

Quiz

PostgreSQL DROP TYPE Statement

6 questions

Learn how to remove user-defined data types from a PostgreSQL database.

Was this helpful?