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 …