Listing the extensions available in PostgreSQL

When you follow this blog regularly you probably already now that PostgreSQL is highly extensible. There are quite a couple of extension which ship by default and are ready to use. How can you know what is there? The most obvious way is to check the documentation. But did you know there are other ways for getting this information?

What you can do to list the available extensions is to check the files on disk at the location where you installed PostgreSQL, in my case:

postgres@pgbox:/u01/app/postgres/product/96/db_2/share/extension/ [PG962] pwd
/u01/app/postgres/product/96/db_2/share/extension
postgres@pgbox:/u01/app/postgres/product/96/db_2/share/extension/ [PG962] ls
adminpack--1.0.sql                  hstore--1.3--1.4.sql                  pageinspect.control                      plperlu--unpackaged--1.0.sql
adminpack.control                   hstore--1.4.sql                       pageinspect--unpackaged--1.0.sql         plpgsql--1.0.sql
autoinc--1.0.sql                    hstore.control                        pg_buffercache--1.0--1.1.sql             plpgsql.control
autoinc.control                     hstore_plperl--1.0.sql                pg_buffercache--1.1--1.2.sql             plpgsql--unpackaged--1.0.sql
...

The issue with this approach is that chances are high that you have no clue what the extensions are about. Better ask the database by checking pg_available_extensions:

postgres=# select * from pg_available_extensions;
        name        | default_version | installed_version |                               comment                                
--------------------+-----------------+-------------------+----------------------------------------------------------------------
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
 plperl             | 1.0             |                   | PL/Perl procedural language
 plperlu            | 1.0             |                   | PL/PerlU untrusted procedural language
 plpython2u         | 1.0             |                   | PL/Python2U untrusted procedural language
 plpythonu          | 1.0             |                   | PL/PythonU untrusted procedural language
 pltcl              | 1.0             |                   | PL/Tcl procedural language
 pltclu             | 1.0             |                   | PL/TclU untrusted procedural language
 adminpack          | 1.0             |                   | administrative functions for PostgreSQL
 bloom              | 1.0             |                   | bloom access method - signature file based index
 btree_gin          | 1.0             |                   | support for indexing common datatypes in GIN
 btree_gist         | 1.2             |                   | support for indexing common datatypes in GiST
 chkpass            | 1.0             |                   | data type for auto-encrypted passwords
...

Here you can check the “comment” column which explains what an extension is about.

There is another catalog view which gives you even more information, e.g. the dependencies between extensions, pg_available_extension_versions:

postgres=# select * from pg_available_extension_versions where requires is not null;
       name        | version | installed | superuser | relocatable | schema |      requires       |                           comment                            
-------------------+---------+-----------+-----------+-------------+--------+---------------------+--------------------------------------------------------------
 earthdistance     | 1.1     | f         | t         | t           |        | {cube}              | calculate great-circle distances on the surface of the Earth
 hstore_plperl     | 1.0     | f         | t         | t           |        | {hstore,plperl}     | transform between hstore and plperl
 hstore_plperlu    | 1.0     | f         | t         | t           |        | {hstore,plperlu}    | transform between hstore and plperlu
 hstore_plpythonu  | 1.0     | f         | t         | t           |        | {hstore,plpythonu}  | transform between hstore and plpythonu
 hstore_plpython2u | 1.0     | f         | t         | t           |        | {hstore,plpython2u} | transform between hstore and plpython2u
 hstore_plpython3u | 1.0     | f         | t         | t           |        | {hstore,plpython3u} | transform between hstore and plpython3u
 ltree_plpythonu   | 1.0     | f         | t         | t           |        | {ltree,plpythonu}   | transform between ltree and plpythonu
 ltree_plpython2u  | 1.0     | f         | t         | t           |        | {ltree,plpython2u}  | transform between ltree and plpython2u
 ltree_plpython3u  | 1.0     | f         | t         | t           |        | {ltree,plpython3u}  | transform between ltree and plpython3u
(9 rows)

Once you installed an extension you have two options for displaying that information. Either you use the psql shortcut:

postgres=# create extension hstore;
CREATE EXTENSION
postgres=# dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.4     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

… or you ask pg_extension:

postgres=# select * from pg_extension ;
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
---------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql |       10 |           11 | f              | 1.0        |           | 
 hstore  |       10 |         2200 | t              | 1.4        |           | 

Btw: Did you know that you can tell psql to show you the actual statement that gets executed when you use a shortcut?

postgres=# set ECHO_HIDDEN on
postgres=# dx
********* QUERY **********
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
ORDER BY 1;
**************************

                           List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.4     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

Happy extending …