Introduction to PostgreSQL Extensions
PostgreSQL extensions are defined as modulus, which was used to supply the extra operators and functions; there are multiple extensions available in PostgreSQL. Create an extension command that will load the new extension into the current schema, which we have connected. We can also load the specified extension from a different schema by specifying the schema’s name. We need to create privileges on objects on which we have created an extension; for creating extensions, we have required the owner or super user privileges is required.
Syntax and Parameter
Below is the syntax of extensions.
1. Create or load extension
Create extension name_of_extension with schema name_of_schema version version_of_extension from old_version_of_extension;
2. Create or load extension using if not exist parameter
Create extension [if not exist] name_of_extension with schema name_of_schema version version_of_extension from old_version_of_extension;
3. Drop extension
Drop extension name_of_extension;
Parameter:
Below is the parameter description syntax of extensions.
- Create extension: This is defined as creating an extension command used to load the new extension. We can load multiple extensions in the single schema to use in the database.
- Name of extension: At the time of loading the extension, we need to define the extension’s name in PostgreSQL.
- With schema: This is defined as load the extensions with specified schema; if we have not defined any schema, then extensions will load the currently connected schema.
- Schema name: This is defined as a schema name which was we have used while loading an extension in PostgreSQL. If we have not defined any schema name, then extensions will load the currently connected schema.
- Version: This is defined as which version we have used for the extension at the loading time.
- Drop extension: We can drop a loaded extension by using the drop extension command.
- The old version of the extension: This is defined as creating an extension in PostgreSQL by using a version of the extension in PostgreSQL.
- If not exist: While using the if not exist keyword, it will not show the error while the same extension is present in the database. At the same time, the notice is issued.
How do Extensions Work in PostgreSQL?
- Below is the working of the extension in PostgreSQL.
- If we need extra functions and operators in PostgreSQL simultaneously, we have to load a specified extension in PostgreSQL.
- The below example shows that we need owner or super user privileges to load the extension in PostgreSQL.
psql -U db_test -d testing
create extension citext;
psql -U postgres -d testing
create extension citext;
- In the above first example, we have to try to create an extension by using db_test user, but it will issue error permission denied creating extension because db_test user doesn’t have superuser privileges.
- In the second example, we have created an extension by using Postgres user; using Postgres user, we have created an extension as context. Postgres users, by default, have superuser privileges.
- In PostgreSQL below, extensions are available.
- Tsearch2
- Timetravel
- Tcn
- Tablefunc
- Sslinfo
- Seg
- Refint
- Postgres_fdw
- Postgis_topology
- Postgis_tiger_geocoder
- Postfis
- Plv8
- Plpgsql
- Plperl
- Plls
- Plcofee
- Pgcrypto
- Pgrouting
- Pgrowlocks
- Pgstattuple
- Pageinspect
- Pg_buffercache
- Pg_freespacemap
- Pg_prewarm
- Pg_stat_statements
- Pg_trgm
- Pg_visibility
- Address_standardizer
- Address_standardizer_data_us
- Autoinc
- Bloom
- Btree_gin
- Btree_gist
- Chkpass
- Citext
- Cube
- Dblink
- Dict_int
- Dict_xsyn
- Earthdistance
- File_fdw
- Moddatetime
- Ltree
- Lo
- Isn
- Intarray
- Insert_username
- Hstore_plperly
- Hstore_plperl
- Hstore
- Fuzzystrmatch
Examples of PostgreSQL Extensions
Below is an example of an extension in PostgreSQL.
1. Load Extension Into the Currently Connected Database
The below example shows that load extension into the currently connected database. We have a loading citext extension.
create extension citext;
\dx
2. Load extension by specifying the schema name
The below example shows that load extension by specifying the schema name parameter. We have a loading bloom extension.
create extension bloom with schema public;
\dx
3. Load Extension by Specifying if Not Exist Parameter
The below example shows that load extension by specifying if not exist parameter. We have a loading postgres_fdw extension.
create extension IF NOT EXISTS postgres_fdw with schema public;
\dx
4. Load Extension by Specifying from Parameter
The below example shows that load extension by specifying from the parameter. We have a loading postgres_fdw extension.
create extension IF NOT EXISTS postgres_fdw with schema public from unpackaged;
\dx
5. Drop Extension
- The below example shows that drop extension in PostgreSQL. We have to drop the extension by using the drop extension command.
- In the below example, we have to drop bloom and citext extension.
drop extension bloom;
drop extension citext;
\dx
Recommended Articles
This is a guide to PostgreSQL Extensions. Here we also discuss the introduction and how do extensions work in postgresql, along with different examples and their code implementation. You may also have a look at the following articles to learn more –
