Implementing a wide range of functionalities, data types, and operators is possible in PostgreSQL. But to implement some advanced functionalities, these functionalities provided by PostgreSQL are not enough. To effectively implement these functionalities, we use extensions.

Extensions are the additional features that upgrade the functionality. These extensions can operate/work like the built-in features in PostgreSQL when they are created and loaded properly. But sometimes these extensions are of no use to us, so we prefer to drop them despite keeping them.

The article teaches us the method we can drop an extension in PostgreSQL. Let’s get started with it.

How to Drop an Extension in PostgreSQL?

When the extensions are of no use we prefer to discard them rather than keep them. Let’s suppose we have a CITEXT extension already existing in our system, we can drop it using the DROP EXTENSION.

Note: We have already learned how to create an extension in PostgreSQL. You can see the article if you want to learn more about the CITEXT extension and the method to create it.

Now we will see how to drop an extension. To drop an extension following is the basic syntax:

DROP EXTENSION exten_name

You simply need to specify the name of the extension you want to drop. To drop the CITEXT extension, the query will be:

DROP EXTENSION CITEXT

After the execution of this query, the extension will be dropped which can be verified from the following output:

But if the extension is being used by any database. In this case, if we want to drop any extension, the following will be the error thrown by PostgreSQL:

To forcibly drop the extension, we will use the CASCADE keyword like this:

This will ensure that the extension has been dropped by PostgreSQL.

We can also verify the deletion of the extension from the side panel. All the extensions are listed in the side panel under the “Extensions” option. The name or the extension will be removed from the extension option.

By doing so you will definitely be able to see drop the extension.

Conclusion

We use extensions to implement some advanced functionality of our code. But if they are not being used it's better to discard/drop them. The extensions can be dropped by using the DROP EXTENSION clause which is followed by the name of the extension. Executing this command will drop/discard the extension. In this write-up, we have learned how to drop an extension in PostgreSQL with proper implementation.