EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Extensions
 

PostgreSQL Extensions

Priya Pedamkar
Article byPriya Pedamkar

Updated May 12, 2023

PostgreSQL Extensions

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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;

PostgreSQL Extensions-1.1

  • 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

PostgreSQL Extensions-1.2

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

PostgreSQL Extensions-1.3

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

Output-1.4

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

Output-1.5

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

Output-1.6

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.

  1. PostgreSQL Like
  2. PostgreSQL Inner Join
  3. Transaction PostgreSQL
  4. PostgreSQL Materialized Views

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW