Managing PostgreSQL extensions

Listing installed extensions

Get a list of all the extensions installed on a database by using the \dx command.

For example, the output for \dx when run on the Databases for PostgreSQL default database shows the only installed extension.

postgres=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

Installing extensions

To install an extension on to a database use CREATE EXTENSION. For example, to install pg_stat_statements on the ibmclouddb database, use the following command:

postgres=> CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

Extensions are installed into the read-only ibm_extension schema. The schema is part of the search_path so extension objects do not need to be qualified with a schema. The change from public schema to ibm_extension schema is necessary to protect the security and integrity of your data.

If you run the \dx command after installing an extension, it appears in the table.

postgres=> \dx
                                     List of installed extensions
        Name        | Version |   Schema      |                        Description
--------------------+---------+---------------+-----------------------------------------------------------
 pg_stat_statements | 1.5     | ibm_extension | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

Database extensions in PostgreSQL are managed per database. If you have multiple databases that you need to install an extension on, run the CREATE command on each database.

Upgrading extensions

If there is a newer version of an extension available than the one you currently have installed, use the ALTER EXTENSION to upgrade it.

Extension-specific notes

Available extensions

See the following list of all available extensions. For the latest list of available extensions on your deployment, use SELECT name FROM pg_available_extensions; in psql.

postgres=> SELECT name FROM pg_available_extensions order by 1;

PostgreSQL Extensions