These extensions are defined to be used as extra operators to perform functions. There are multiple extensions present in PostgreSQL; some of them are explained in this article.
Syntax of creating an extension
CREATE EXTENSION [IF NOT EXISTS ] extensionname
[ WITH ] [ SCHEMA schemaname ]
[ VERSION versionof_extension ]
[ FROM oldversion ]
[ CASCADE ]
Description of the syntax
A CREATE extension keyword creates a new extension in the PostgreSQL. The process of loading an extension uses the same privileges that are required to create the component objects. This requires the superuser or the owner to proceed forward.
IF NOT EXISTS
If any extension with the same name you have used in the create statement already exists, this will not create an error. But only a notice id is displayed to notify the user about the extension.
Schema_name
To install or load any extension, a schema must exist in which you want to add the extension. If the schema is not specified in the command, then the current schema that uses the current default object creation is used by default.
CASCADE
These features install any extension automatically, on which any extension depends that is not already installed. Whereas the dependencies are installed automatically.
For the implementation process, open the psql shell by providing a username and password; this will proceed forward to start with the connection of Postgres database with Postgres user.
Once the connection is developed, now you can apply any command. Below we will see the working of the PostgreSQL extension in the PostgreSQL database. If we want to add some extra functionality or operations in the form of a PostgreSQL extension, we need to add some extensions.
Notes
Before using the create extension command to load any extension in the database, the files that support the extensions should be installed. The extensions used by PostgreSQL are supposed to be secure from the attacks done at the time of installation.
These are the built-in functionalities that are present in PostgreSQL. You just need to add them by using their name in the command. For example, we have used the ‘citext’ extension. The simple syntax used to enable any extension in PostgreSQL is:
>> psql –u db_postgres –d testing
>> CREATE extension citext;
The above commands depict that PostgreSQL requires superuser or the owner privileges to load the PostgreSQL extensions. While using db_postgres did not work, and the extensions were not loaded, we tried the name of the user ‘Postgres’ to load the extensions.
After loading, we selected an already existing extension to get loaded in PostgreSQL. Here we have selected an extension ‘citext’.
You can see that the extension is created. To see the description and names of all the extensions added to Postgres, we use a ‘\dx’.
Already three extensions were added, in addition, a new extension citext is shown in the names of all the extensions. The description of the extension includes the version and schema of each extension along with the information, including the data types, administrative functions, and procedural languages as well. The citext extension is responsible for the strings of case-sensitive characters.
We have added an extension only without specifying the name of the schema or version. But an extension can be added along with the schema name, just like the example below. A schema is a namespace that has database objects like tables, indexes, data types, functions, etc. That’s why we use the schema name to easily access all the features of data present in the schema. The default schema is ‘public’.
This extension is created. The schema is selected as public. Again we will see the description of extensions. A full list will describe the purpose of each description, like the bloom extension, which will have a bloom access method including a file-based single index.
Now we will load the extension with the phenomenon if it does not exist along with the name of the schema.
Again check the newly installed extension. This extension helps the functionality of Postgres in enhancing the data type to store the sets of keys and values.
To delete an extension, the below command will be used. For example, we want to remove “hstore” to use the following command.
There is another way to use the command of adding an extension. In this approach, we first set the search path in the schema and then create the extension in that particular schema.
You can see that first, the search_path is set and the ‘CREATE EXTENSION’ message shows that the extension is created. In this way, an extension is added and deleted from the PostgreSQL shell.
Extension creation via pgAdmin
Now we will see the addition of indexes through the pgAdmin panel of PostgreSQL. By providing a password, you will proceed further in the database panel.
Navigate towards the left panel on the dashboard, expand the server’s options, and expand the PostgreSQL database; all databases will be shown, open the concerned database like ‘Postgres’.
In this section, you will find an option of extension; expand that then all the created extensions will be shown.
By using a right-click on the extensions option, a drop-down is opened, you can create a new extension by selecting the create option.
On selecting the create option, a new window is opened. Now you need to fill all the text boxes. For example, select a name and a drop-down of all the extensions opened. We have selected the ‘autoinc’ extension. A comment option is not mandatory; you can leave it empty.
After filling in the general information, you can go to the Definition part; here, the schema of the extension and the version are mentioned. We will fill the space with ‘public’ as Schema and the version as 1.0.
After adding, save the changes. And then, refresh the extensions; you will see that a new extension is added to the already existing extension.
In the pgAdmin panel, if you want to delete any extension, you need to right-click on the extension you want to remove. For instance, here, we want to remove the ‘autoinc’ option and select the option DELETE/DROP’ from the drop-down options.
After that, a dialogue box appears that will verify the deletion. Select ‘yes’ to proceed with the process of deletion.
Conclusion
The article about the ‘Postgres create extension’ includes the process of creating extensions in both the psql shell and in the pgAdmin. Some commonly used extensions are also listed in the article. Extensions enhance the functionalities of the PostgreSQL objects that are already present in the database; furthermore, the user must know the database and the schema creation before installing the extensions.
