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.
• Most extensions install their objects into a dedicated schema (e.g., extensions) or public. Using a dedicated schema keeps 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 the latest available version:

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: Any user can query pg_extension. CREATE EXTENSION requires superuser or the pg_extension_owner role (PostgreSQL 15+).
• 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_extensions view marks these with trusted = true.
• Extension scripts: Extension files live in $sharedir/extension/ on the server. If an extension is missing from pg_available_extensions, its .control file is not present on disk.

References

• PostgreSQL: Extensions
• PostgreSQL: pg_extension catalog
• PostgreSQL: pg_available_extensions view