Data types are mandatory to define the column types while creating a Postgres table. PostgreSQL offers many built-in data types. However, we can also create custom data types to use them where needed. We can also discard the user-defined data types that already exist and are no longer in use. In this article, we will learn how to drop a user-defined data type in PostgreSQL. So let’s get started.

How to Drop a Custom/User-Defined Type in PostgreSQL?

We can drop the custom data type by simply using the DROP DDL command of PostgreSQL. The basic syntax to drop any user-defined data type is:

DROP TYPE IF EXISTS user_def_type_name;

We simply need to write the name of the user-defined data type that we want to delete/drop. The command used is DROP TYPE IF EXIST, this command simply drops the type if it exists otherwise it won’t return an error, unlike the simple DROP TYPE command. Let’s learn about dropping a type with the help of an example.

Example 1: Dropping a Custom/User-Defined Type in PostgreSQL

The user-defined data type name “solar_system” is already created in my database which can be seen under the “Types” section.

Let’s try to drop it using the DROP TYPE statement. The query for this looks like:

DROP TYPE IF EXISTS solar_system;

The query will simply drop the solar_system type that is created by the user itself.

We can see that the user-defined data type has been dropped.

Example 2: Dropping a Non-Existing Custom/User-Defined Type in PostgreSQL

Now if we try to drop a type that does not exist using the DROP TYPE IF EXISTS statement, the query will raise a notice instead of showing an error. Let’s try deleting a type “non_existing_type” that does not exist.

DROP TYPE IF EXISTS non_existing_type;

The output of this query is:

We can see that a notice is raised instead of throwing an error and then ships the statement as the custom_type does not exist.

Example 3: Dropping a Custom/User-Defined Type Forcibly in PostgreSQL

The user-defined data type name “scholarship_details” is already created in my database which can be seen under the “Types” section.

Let’s try to discard it using the DROP TYPE statement. The query for this looks like:

DROP TYPE IF EXISTS scholarship_details;

The above query is responsible for dropping the user-defined data type named “scholarship_details” but after the execution of this query, we see that the query throws an error. The error is:

The error shows that the type “scholarship_details” is in use by some object. So if we really want to drop this type we will forcibly apply the DROP statement on it. This can be done by using the CASCADE keyword. This always drops the object forcibly even if this is used by some object. The query will be manipulated like this:

DROP TYPE IF EXISTS scholarship_details CASCADE;

This query will drop the user-defined data type even if it is used by some object. The query will look like this:

The above query has dropped the user-defined data type successfully. We can verify this by executing the “\dT” in psql or looking for the name of the type in the side panel of the pgAdmin under the option of “Types”. We will see that the user-defined data type will no longer be present under it like this:

We can see that our defined data type is no longer present. This ensures the deletion of the data type we defined.

Conclusion

We can drop the already existing user-defined data type that is no longer in our use by using the DROP TYPE IF EXISTS clause. This statement deletes the user-defined data type if it exists, if not it will not throw an error. If that data type is already in use by some objects in the database, then we have to add a CASCADE keyword at the end of the query to forcibly drop the type. In this write-up, we have learned to delete the custom data types in PostgreSQL with valid execution.