Before digging into the extensions that are available, it is a good idea to take a look at how extensions work in the first place. Understanding the inner workings of the extension machinery can be quite beneficial.

Let's take a look at the syntax first:

test=# h CREATE EXTENSION 
Command:     CREATE EXTENSION
Description: install an extension
Syntax:
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]
             [ VERSION version ]
             [ FROM old_version ]
             [ CASCADE ]
URL: https://www.postgresql.org/docs/12/sql-createextension.html

When you want to deploy an extension, simply call the CREATE EXTENSION command. It will check for the extension and load it into your database. Note that the extension will be loaded into a database and not into the entire database instance.

If we are loading an extension, we can decide on the schema that we want to use. Many extensions can be relocated so that the user can choose which schema to use. Then, it is possible to decide on a specific version of the extension. Often, we don't want to deploy the latest version of an extension because the client may be running outdated software. In such cases, it may be handy to be able to deploy any version that's available on the system.

The FROM old_version clause requires some more attention. Back in the old days, PostgreSQL didn't support extensions, so a lot of unpackaged code is still around. This option causes the CREATE EXTENSION clause to run an alternative installation script that absorbs existing objects into the extension, instead of creating new objects. Make sure that the SCHEMA clause specifies the schema containing these preexisting objects. Only use it when you have old modules around.

Finally, there is the CASCADE clause. Some extensions depend on other extensions. The CASCADE option will automatically deploy those software packages, too. The following is an example:

test=# CREATE EXTENSION earthdistance;
ERROR: required extension "cube" is not installed
HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too.

The earthdistance module implements great-circle distance calculations. As you may already know, the shortest distance between two points on the Earth cannot be followed in a straight line; instead, a pilot has to adjust his/her course constantly to find the fastest route when flying from one point to the other. The thing is, the earthdistance extension depends on the cube extension, which allows you to perform operations on a sphere.

To automatically deploy this dependency, the CASCADE clause can be used, as we described previously:

test=# CREATE EXTENSION earthdistance CASCADE;
NOTICE: installing required extension "cube" CREATE EXTENSION

In this case, both extensions will be deployed.