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];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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, ...;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Basic DROP TYPE statement example #

First, create a new type called contact:

CREATE TYPE contact AS ( 
    phone TEXT, 
    email TEXT 
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

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

DROP TYPE IF EXISTS contact;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, attempt to drop the address type:

DROP TYPE address;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.Code language: plaintext (plaintext)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 customersCode language: plaintext (plaintext)

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 #