Updated May 12, 2023
Introduction to PostgreSQL Extensions
PostgreSQL extensions are defined as modulus, used to supply the extra operators and functions; multiple extensions are available in PostgreSQL. Create an extension command to load the new Extension into the current schema, which we have connected. We can also load the specified Extension from a different schema by selecting 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 the 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 creates an extension command to load the new Extension. We can load multiple attachments in a single schema to use in the database.
- Name of Extension: When 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 expressed 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. Extensions will load the currently connected schema if we have not defined any schema name.
- 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 using the command.
- The old version of the Extension: This is defined as creating an extension in PostgreSQL by using an understanding of the Extension in PostgreSQL.
- If not exist: Using the if a not live keyword will not show the error while the same Extension is 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 must 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 first example above, we have to create an extension by using db_test user. Still, it will issue an error permission denied creating an extension because the db_test user doesn’t have superuser privileges.
- In the second example, we have created an extension by using a Postgres user; using a 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
We hope that this EDUCBA information on “PostgreSQL Extensions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
