Work with extensions

Eugenia Kuzina

PostgreSQL provides a way to extend the database functionality by bundling SQL objects into a package and using them as a module. Such modules are called extensions. Extensions can include functions, operators, types, and others.

View available extensions

Use psql to view the installed extensions:

  • The \dx meta-command lists installed extensions.

  • The \dx+ meta-command displays installed extensions and their associated objects.

The pg_available_extensions view contains the extensions available for installation. Use the following code to list them:

SELECT * FROM pg_available_extensions;

You can also find the list of available extensions in the Supported extensions article.

The pg_extension catalog stores information about the installed extensions. In ADP, the plpgsql extension is preinstalled in the postgres database.

Use the following code to check installed extensions:

SELECT * FROM pg_extension;

Result:

  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 14472 | plpgsql            |       10 |           11 | f              | 1.0        |           |

When creating a new database, all extensions that are installed in the default database template template1 will be created in a new database (in ADP, in template1, only plpgsql is pre-installed). See Template Databases.

Manage extensions

You can create, alter, and drop extensions using corresponding commands.

CAUTION
If you use PL/Perl, PL/PerlU, PL/Python3U, PL/Tcl, or PL/TclU procedural languages that require additional packages, make sure that all packages for corresponding extensions are installed on all ADP nodes — the leader and all replicas. Otherwise, the cluster can be damaged during a minor/major upgrade.

Create an extension

CREATE EXTENSION creates a new extension in the current database. Extensions should have unique names within the same database.

When CREATE EXTENSION is called, PostgreSQL runs the extension script file. The script creates new SQL objects, for example, functions, data types, operators, and index support methods. CREATE EXTENSION also records the identifiers of all the created objects, so they can be dropped if DROP EXTENSION is run for this extension.

The user who runs CREATE EXTENSION becomes the extension owner and the owner of any objects created by the extension script.

The following code installs the pg_stat_statements extension:

CREATE EXTENSION pg_stat_statements;

If a script file of the extension specified in the CREATE EXTENSION command is not found, an error indicating this will be generated. In this case, install an appropriate package that contains this extension.

For more information on the CREATE EXTENSION command, refer to CREATE EXTENSION.

Drop an extension

DROP EXTENSION removes extensions from the database. Dropping an extension causes its component objects to be also dropped.

The following code uses the CASCADE option to drop an extension, all dependent objects, and objects that depend on the dependent objects:

DROP EXTENSION plperlu CASCADE;

For additional information, see DROP EXTENSION.

Alter an extension

ALTER EXTENSION modifies an extension.

The following code changes the schema of the hstore extension to schema1:

ALTER EXTENSION hstore SET SCHEMA schema1;

For additional information, see ALTER EXTENSION.

Found a mistake? Seleсt text and press Ctrl+Enter to report it