List Installed Extensions in PostgreSQL

How to List Installed Extensions in PostgreSQL

PostgreSQL extensions package additional functionality — data types, functions, operators, and index methods — that can be installed into a database with CREATE EXTENSION. Common examples include pg_stat_statements for query performance tracking, uuid-ossp for UUID generation, postgis for geographic data, and pg_trgm for fuzzy text search.

Before modifying a database or diagnosing unexpected behavior, it is useful to know exactly which extensions are installed, which versions are running, and which schema their objects live in.

Sample Code

List all installed extensions in the current database:

 1SELECT
 2    e.extname                          AS extension_name,
 3    e.extversion                       AS installed_version,
 4    n.nspname                          AS schema,
 5    e.extrelocatable                   AS relocatable,
 6    c.description                      AS description
 7FROM
 8    pg_catalog.pg_extension e
 9    JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
10    LEFT JOIN pg_catalog.pg_description c
11        ON c.objoid = e.oid
12        AND c.classoid = 'pg_catalog.pg_extension'::regclass
13ORDER BY
14    e.extname;

Check what extensions are available (installed or not):

1SELECT
2    name,
3    default_version,
4    installed_version,
5    comment
6FROM
7    pg_catalog.pg_available_extensions
8ORDER BY
9    name;

Find extensions with an available upgrade:

 1SELECT
 2    name,
 3    installed_version,
 4    default_version
 5FROM
 6    pg_catalog.pg_available_extensions
 7WHERE
 8    installed_version IS NOT NULL
 9    AND installed_version <> default_version
10ORDER BY
11    name;

Notes: pg_extension works on PostgreSQL 9.1 and later (extensions were introduced in 9.1). pg_available_extensions requires the extension files to be present on the server's file system — it reflects what the server can install, not just what is installed.

Code Breakdown

• pg_catalog.pg_extension — System catalog that stores one row for each extension installed in the current database. Extensions are per-database, not per-cluster.
• extname — The extension name as used in CREATE EXTENSION extname.
• extversion — The version string of the currently installed extension.
• extnamespace — OID of the schema where most of the extension's objects are created.
• extrelocatable — Boolean. If true, the extension can be moved to a different schema with ALTER EXTENSION ... SET SCHEMA.
• pg_catalog.pg_namespace — Joined to convert extnamespace OID to a readable schema name.
• pg_catalog.pg_description — Provides the human-readable description of the extension, filtered by classoid to the pg_extension catalog.
• pg_catalog.pg_available_extensions — A view (not a table) that reads from the server's extension files on disk. Shows installed_version as null for extensions that are available but not yet installed.

Key Points

• Extensions are installed per database. Running CREATE EXTENSION pg_stat_statements in mydb does not install it in otherdb.
• The pg_catalog schema itself is never listed as an extension — its objects are built into PostgreSQL.
• Each extension's objects go into a schema you can choose with the SCHEMA clause of CREATE EXTENSION, if the extension is relocatable. Many teams use a dedicated schema (e.g., extensions) to keep public clean.
• pg_stat_statements must also be added to shared_preload_libraries in postgresql.conf to function — installing it with CREATE EXTENSION alone is not enough.

Insights and Explanations

Checking if a specific extension is installed:

1SELECT extname, extversion
2FROM pg_catalog.pg_extension
3WHERE extname = 'pg_stat_statements';

Returns one row if installed, zero rows if not.

Installing an extension:

1CREATE EXTENSION IF NOT EXISTS pg_trgm
2    SCHEMA extensions;

Upgrading an extension to its default version (the version named in the extension's control file):

1ALTER EXTENSION pg_trgm UPDATE;

Removing an extension:

1DROP EXTENSION pg_trgm;

This removes the extension and all objects it created. Use CASCADE to also drop dependent objects.

Common useful extensions:

Additional Considerations

• Permissions: The queries above only read system catalogs. CREATE EXTENSION ordinarily requires the same privileges needed to create the extension's component objects, which for many extensions means superuser.
• Trusted extensions: PostgreSQL 13 introduced trusted extensions, which can be installed by non-superusers who hold the CREATE privilege on the database. The pg_available_extension_versions view marks these with trusted = true.
• Extension scripts: Extension control files live in $sharedir/extension/ on the server (PostgreSQL 18 adds extension_control_path for additional locations). If an extension is missing from pg_available_extensions, its .control file is not present in those locations.

References

• PostgreSQL: Extensions
• PostgreSQL: pg_extension catalog
• PostgreSQL: pg_available_extensions view
• PostgreSQL: CREATE EXTENSION
• PostgreSQL: ALTER EXTENSION
• Crunchy Data: Examining Postgres Upgrades with pg_upgrade — uses pg_available_extensions and ALTER EXTENSION ... UPDATE after an upgrade