The Postgresql database management system is used to store data in tabular form. The database applies several functions to the data stored in the relationship. To enhance and support these features in a PostgreSQL database, there is an approach to extending database functionality with SQL objects.
These extensions are intended to be used as additional statements to execute functions. PostgreSQL comes with several extensions; some of them are described in this article.
Syntax for creating an extension
CREATE EXTENSION [IF NOT EXISTS ] extensionname
[ WITH ] [ SCHEMA schemaname ]
[ VERSION versionof_extension ]
[ FROM oldversion ]
[ CASCADE ]
The CREATE extension keyword creates a new extension in a PostgreSQL file. The loading process of an extension uses the same privileges that are required to create component objects. This requires superuser or owner to proceed.
IF NOT EXISTS
If any extension with the same name that you used in the create statement already exists, this will not raise an error. But only the notification id is displayed to notify the user about the extension.
In order to install or load any extension, there must be a schema in which you want to add the extension. If no schema is specified in the command, then the current schema is used by default, using the current default object creation.
These functions automatically install any extension that any extension not yet installed depends on. Whereas the dependencies are installed automatically.
For the implementation process, open a psql shell with a username and password; this will proceed to start by connecting a Postgres database to a Postgres user.
Once the connection is established, you can now apply any command. Below we will see the PostgreSQL extension in action on a PostgreSQL database. If we want to add some additional functionality or operations as a PostgreSQL extension, we need to add some extensions.
Before you can use the create extension command to load any extension into the database, you must install files that support the extensions. It is assumed that the extensions used by PostgreSQL are protected from attacks made during installation.
These are built-in functions present in PostgreSQL. You just need to add them using their name in the command. For example, we used the citext extension. The simple syntax used to include any extension in PostgreSQL is:
>> CREATE extension <name_of_extension>
>> psql -u db_postgres -d testing
>> CREATE extension citext;
The above commands show that PostgreSQL requires superuser or owner permissions to load PostgreSQL extensions. Although using db_postgres didn’t work and the extensions weren’t loaded, we tried to load the extensions using the “Postgres” username.
>> psql –U Postgres –d testing;
After loading, we selected an already existing extension to load into PostgreSQL. Here we have chosen the “citext” extension.
>> CREATE extension citext;
You can see that the extension has been created. To see the description and names of all extensions added to Postgres, we use ‘\dx’.
Three extensions have already been added, in addition, the new citext extension is displayed in the names of all extensions. The extension description includes the version and schema of each extension, as well as information including data types, administrative functions, and procedural languages. The citext extension is responsible for case-sensitive character strings.
We added the extension only without specifying the schema name or version. But along with the schema name, you can add an extension, as in the example below. A schema is a namespace that has database objects such as tables, indexes, data types, functions, etc. This is why we use the schema name to easily access all the features of the data present in the schema. The default schema is public.
>> CREATE extension bloom WITH public schema;
This extension has been created. The scheme is selected as public. Again we will see the description of the extensions. The full list will describe the purpose of each description, such as the bloom extension, which will have an access method to bloom, including a single file index.
We will now load the feature extension if it does not exist along with the schema name.
>> CREATE extension IF NOT EXISTS store WITH public schema;
Check the newly installed extension again. This extension helps the Postgres functionality to improve the data type for storing sets of keys and values.
To remove the extension, the below command will be used. For example, we want to remove “hstore” in order to use the following command.
>> DROP extension hstore;
There is another way to use the add extension command. In this approach, we first set the search path in a schema and then we create an extension in that particular schema.
>> SET search_path = addons;
>> CREATE extension hstore schema public;
You can see that first, the search_path is set and the message “CREATE EXTENSION” shows that the extension has been created. So the extension is added and removed from the PostgreSQL wrapper.
Creating an extension via pgAdmin
We will now see adding indexes through the pgAdmin PostgreSQL panel. Entering a password will take you further to the database panel.
Go to the left pane on the dashboard, expand server options and expand PostgreSQL database; all databases will be shown, open the appropriate database, for example “Postgres”.
In this section you will find the possibility of expansion; expand this, then all created extensions will be shown.
Right-clicking on an extension option opens a drop-down list where you can create a new extension by selecting the create option.
When you select the create option, a new window opens. Now you need to fill in all text fields. For example, select the name and drop-down list of all open extensions. We chose the “autoinc” extension. The comment option is optional; you can leave it blank.
After filling in the general information, you can proceed to the “Definition” part; it mentions the extension scheme and version. We will fill the space with “public” as the schema and version 1.0.
After adding, save your changes. Then update the extensions; you will see that the new extension is added to the already existing extension.
In the pgAdmin panel, if you want to remove any extension, you need to right click on the extension you want to remove. For example, here we want to remove the “autoinc” option and select the “REMOVE/REMOVE” option from the dropdown.
After that, a dialog box will appear that will confirm the deletion. Select “yes” to proceed with the uninstall process.
The article on “Postgres create extension” includes the process of creating extensions in both the psql shell and pgAdmin. Some commonly used extensions are also listed in the article. Extensions extend the functionality of PostgreSQL objects that are already present in the database; in addition, the user must know the database and schema creation before installing extensions.