How to List Extensions Installed in a PostgreSQL Database Using psql

Extensions in PostgreSQL are powerful add-ons that enhance database functionality. Whether you’re looking to use features like PostGIS for spatial data or pg_trgm for text similarity, knowing how to list installed extensions in a database is crucial. This guide will show you how to achieve this using the psql command-line tool.

Prerequisites

Before proceeding, ensure you have:

1. Access to psql: The psql client should be installed on your system.
2. Database credentials: You’ll need the host, port, database name, username, and password for the PostgreSQL instance.

Steps to List Installed Extensions

1. Connect to the Database

Open your terminal and connect to the desired PostgreSQL database using the psql command. For example:

psql -h <host> -p <port> -U <username> -d <database_name>

You’ll be prompted to enter the password for the specified user.

2. Use the \dx Command

Once connected to the database, use the \dx meta-command to list all installed extensions:

\dx

This command will output a table containing the following columns:

• Name: The name of the extension.
• Version: The version of the installed extension.
• Schema: The schema where the extension is installed.
• Description: A brief description of the extension.

Example Output:

List of installed extensions
Name | Version | Schema | Description
———–+———+———-+——————————————–
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.3.2 | public | PostGIS geometry, geography, and raster spatial types
pg_trgm | 1.5 | public | text similarity measurement and index searching

3. Query the pg_extension System Catalog (Optional)

If you prefer a SQL-based approach, you can query the pg_extension system catalog. Run the following query:

SELECT extname AS name, extversion AS version, nspname AS schema, description
FROM pg_extension
JOIN pg_namespace ON pg_extension.extnamespace = pg_namespace.oid;

Example Output:

name | version | schema | description
———–+———+———–+—————————————–
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.3.2 | public | PostGIS geometry, geography, and raster spatial types
pg_trgm | 1.5 | public | text similarity measurement and index searching

4. Filter Extensions (Optional)

You can add a WHERE clause to the SQL query to filter specific extensions. For example, to find only the postgis extension:

SELECT extname AS name, extversion AS version, nspname AS schema, description
FROM pg_extension
JOIN pg_namespace ON pg_extension.extnamespace = pg_namespace.oid
WHERE extname = ‘postgis’;

Listing installed extensions in a PostgreSQL database using psql is straightforward. Whether you prefer the convenience of the \dx command or the flexibility of a custom SQL query, both methods provide the details you need. Understanding the installed extensions in your database helps you manage and optimize its functionality effectively.