Work with extensions

PostgreSQL provides a way to extend the functionality of a database by bundling SQL objects into a package and using them as a unit. This functionality is implemented as PostgreSQL extensions.

Extensions are modules that provide additional functionality: functions, operators, types, etc.

View available extensions

You can use psql to view available extensions:

  • The \dx command lists installed extensions.

  • The \dx+ 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;

The contrib and PostGIS extensions

The contrib and PostGIS extensions are preinstalled in the ADPG cluster.

The contrib extension includes a lot of modules that are implemented as separate extensions. The contrib extension contains porting tools, analysis utilities, and plug-in features that the core PostgreSQL system does not contain. For additional details on available extension modules from the contrib directory, refer to Additional supplied modules.

PostGIS is a spatial database extension for PostgreSQL. It adds support for geographic objects and allows you to run location queries in SQL. PostGIS also offers many features rarely found in other competing spatial databases. Refer to PostGIS Feature List for more details.

Manage extensions

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

Create an extension

CREATE EXTENSION loads a new extension into the current database. An extension with the same name cannot be loaded.

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 that they can be dropped if DROP EXTENSION is run.

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

If a matching extension is not found, CREATE EXTENSION reports that the extension script file does not exist.

If an extension cannot be found, install an appropriate package that contains this extension.

The following code creates the pg_stat_statements module, which is included in the contrib extension:

CREATE EXTENSION pg_stat_statements;

For more information on this 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, including version upgrades.

The following code updates the hstore module to version 2.0:

ALTER EXTENSION hstore UPDATE TO '2.0';

For more information, refer to ALTER EXTENSION.

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