PostgreSQL extensions enhance the functionality of the database system by providing additional operators, functions, and valuable features. In Postgres, the "CREATE EXTENSION" command is utilized to add a new extension to the current schema of the connected database. However, to retrieve the list of installed extensions, various methods are used in PostgreSQL.

This write-up will illustrate different commands and queries to show the installed extensions in Postgres.

How to Show Installed Extensions in PostgreSQL?

Postgres provides various commands that help us get the list of installed extensions. In this post, we will discuss the below-mentioned methods to get the list of installed extensions:

• Using “\dx” Command.
• Using “pg_extension” Catalog.
• Using “pgAdmin”.

How to Show Installed Extensions in Postgres Using \dx Command?

“\dx” is a meta-command in Postgres that executes in psql and retrieves the list of installed extensions. The retrieved list provides all the necessary details, such as extension name, version, schema name in which the extension is installed, and description. The following snippet demonstrates how the “\dx” command work in Postgres:

\dx

The “\dx+” command can be utilized to get detailed information about the installed extensions and the associated objects:

\dx+

How to Show Installed Extensions in Postgres Using pg_extension Catalog?

Postgres provides a system catalog named “pg_extension” that keeps the information about the installed extensions. Use the pg_extension catalog with the SELECT command to fetch the extensions’ details, such as extension name, version, extension owner, default schema, etc. The below code snippet shows how the pg_extension catalog work in Postgres:

SELECT * FROM pg_extension;

How to Show Installed Extensions in Postgres Using pgAdmin?

pgAdmin is a popularly used graphical user interface (GUI) for Postgres and is used to perform various database-related tasks, such as database creation, managing tables, etc. Users can use pgAdmin to get the list of installed extensions without even executing any query. For this purpose, open pgAdmin, navigate to the “Servers” tree, expand the “databases” tab, select a database, and head into the “Extensions” section:

That’s all about getting information about the installed extensions in PostgreSQL.

Conclusion

In PostgreSQL, the “\dx” command, “pg_extension” catalog, and “pgAdmin” are used to get the list of installed extensions. The retrieved list provides all the necessary details regarding the installed extensions, such as extension name, version, schema name in which the extension is installed, and description. This write-up has provided a comprehensive guide on showing the list of installed extensions in PostgreSQL.