Introduction to PostgreSQL Extensions
PostgreSQL extensions is defined as modulus which was used to supply the extra operators and functions, there are multiple extensions available in PostgreSQL. Create extension command will load the new extension into the current schema which was we have connected, also we can loads the specified extension from different schema by specifying the name of the schema. 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 to create an extension command is used to load the new extension. We can load multiple extensions in the single schema to use into the database.
- Name of extension: At the time of loading extension we need to define the name of the extension 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 currently connected schema.
- Schema name: This is defined as 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 time of loading.
- Drop extension: We can drop loaded extension by using drop extension command.
- Old version of extension: This is defined as creating an extension in PostgreSQL by using a version of extension in PostgreSQL.
- If not exist: While using if not exist keyword it will not shows the error while the same extension is present into the database. At the same time, the notice is issued.
How do Extensions Work in PostgreSQL?
- Below is the working of extension in PostgreSQL.
- If we need extra function and operators in PostgreSQL the same time we have loading 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 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 extension by using Postgres user, using Postgres user we have created 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 the 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 loading citext extension.
create extension citext;
\dx
2. Load extension by specifying schema name
The below example shows that load extension by specifying 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 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 parameter. We have 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 dropping extension by using drop extension command.
- In the below example, we have dropping 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 its code implementation. You may also have a look at the following articles to learn more –
