There's more…

Another system view, pg_available_extension_versions, shows all the versions that are available for each extension. It can be valuable when there are multiple versions of the same extension available at the same time—for example, when making preparations for an extension upgrade.

When a more recent version of an already installed extension becomes available to the database server, for instance, because of a distribution upgrade that installs updated package files, the superuser can perform an upgrade by issuing the following command:

ALTER EXTENSION mytext UPDATE TO '1.1';

This assumes that the author of the extension taught it how to perform the upgrade.

Extensions interact nicely with logical backup and restore nicely, a topic that will be fully discussed in Chapter 11, Backup and Recovery. As an example, if your database contains the cube extension, then you will surely want a single line (CREATE EXTENSION cube) in the dump file instead of lots of lines recreating each object individually, which is inefficient and also dangerous.

The use of CASCADE in a CREATE statement only applies to extensions, because for other object types, the dependency is not predefined in the object metadata, and only exists after creating a specific object (for example, a foreign key).

Remember that CREATE EXTENSION ... CASCADE will only work if all the extensions it tries to install have already been placed in the appropriate location.