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.
You can use psql to view available extensions:
\dxcommand lists installed extensions.
\dx+command displays installed extensions and their associated objects.
pg_available_extensions view contains the extensions available for installation. Use the following code to list them:
SELECT * FROM pg_available_extensions;
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.
You can create, alter, and drop extensions using corresponding commands.
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 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 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.