How to List Installed Extensions in PostgreSQL Quickly

To list installed extensions in PostgreSQL, use the SQL command SELECT * FROM pg_extension;. This query shows all extensions currently installed in your database.

Syntax

The basic syntax to list installed extensions is:

SELECT * FROM pg_extension;

Here, pg_extension is a system catalog table that stores information about all extensions installed in the current database.

sql

SELECT * FROM pg_extension;

Example

This example shows how to list all installed extensions with their names and versions.

sql

SELECT extname AS extension_name, extversion AS version FROM pg_extension ORDER BY extension_name;

Output

 extension_name    | version 
------------------+---------
 citext           | 1.6     
 plpgsql          | 1.0     
 pg_stat_statements | 1.9    
(3 rows)

Common Pitfalls

Some common mistakes when listing extensions include:

• Running the query in the wrong database: Extensions are installed per database, so you must connect to the correct one.
• Expecting extensions to appear if they are not installed: The query only shows installed extensions, not available ones.
• Confusing pg_extension with pg_available_extensions: The latter shows all extensions that can be installed, not those installed.

sql

/* Wrong: lists available but not installed extensions */
SELECT * FROM pg_available_extensions;

/* Right: lists installed extensions */
SELECT * FROM pg_extension;

Quick Reference

Summary tips for listing PostgreSQL extensions:

Command	Description
SELECT * FROM pg_extension;	Lists all installed extensions in the current database.
SELECT * FROM pg_available_extensions;	Lists all extensions available to install.
\dx	psql command to list installed extensions.
CREATE EXTENSION extension_name;	Command to install a new extension.

Key Takeaways

Use SELECT * FROM pg_extension; to see installed extensions in the current database.

Extensions are installed per database, so connect to the right one before querying.

pg_available_extensions shows extensions you can install, not those installed.

In psql, the shortcut \dx also lists installed extensions.

Always check extension versions to ensure compatibility.