PostgreSQL offers a wide range of functionalities, data types, aggregates, and operators. But sometimes these are not enough to implement the functionality we want. To cater to the shortcomings in functionality, extensions came to the rescue. The extensions are the add-ons that enhance the functionality. These can work like the built-in features when created in PostgreSQL.

The content of this article will revolve around the way we can create an extension in PostgreSQL.

How to Create an Extension in PostgreSQL?

As we have discussed above, the extensions that are used to enhance the functionality are present in the database as add-ons. They need to be created to be used in PostgreSQL. Let’s consider the case of CITEXT extension.

The CITEXT is basically a data type that is a case-insensitive data type that allows us to do text comparisons without being case-sensitive. For instance, if we wish to get the data where the name of the student is “Peter”. If we have declared the variable with some data type other than CITEXT and we fetch the data using the small casing, the query will not return the correct results. To understand the CITEXT data type, you can head over to our PostgreSQL CITEXT Data Type article to get more clarity.

Now if we are using this CITEXT data type for the first time it will throw an error like this:

This is because this data type needs an extension to work. We have to create the CITEXT extension to resolve this error.

To create an extension, the following is the basic syntax:

CREATE EXTENSION exten_name

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

CREATE EXTENSION CITEXT

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

We can also verify the creation of the extension from the side panel. All the extensions are listed in the side panel under the “Extensions” option.

In case you do not see your created extension, just refresh the extension option by right-clicking on the extension option like this.

By performing these steps, you will surely be able to see your created extension.

Now running the query having CITEXT data type won’t cause an error and will definitely work fine. So this is how extensions are created.

Conclusion

We can enhance the functionality of our code by using the extensions that work as built-in features once they are created and loaded. Using the extensions before they are created results in errors. The extensions can be created by using the CREATE EXTENSION clause which is followed by the name of the extension. In these above-given paragraphs, we have learned to create an extension in PostgreSQL.