Database Reference
In-Depth Information
Heroku Postgres extensions
PostgreSQL allows its functionality to be extended with additional modules called exten-
sions. Many of these extensions aren't installed by default in PostgreSQL, because they
aren't part of its core and mainly because they are specific to a limited audience. In addi-
tion, some extensions are experimental, but nothing prevents their use.
Heroku Postgres provides some interesting extensions for data types, functions, statistics,
index types, languages, searches, and many others. In the next sections, you will learn
about each of these extensions, with simple samples, and you will understand how they
work.
The first step to get started is to discover the list of available extensions in your database; it
may vary according your database plan. You should do it using the following command:
$ echo 'show extwlist.extensions' | heroku pg:psql --app
your-app-name
---> Connecting to HEROKU_POSTGRESQL_CHARCOAL_URL
(DATABASE_URL)
extwlist.extensions
-------------------------------------------------------------------btree_gist,chkpass,citext,cube,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,isn,ltree,pg_stat_statements,pg_trgm,pgcrypto,pgrowlocks,pgstattuple,plpgsql,plv8,postgis,postgis_topology,postgres_fdw,tablefunc,unaccent,uuid-ossp
These extensions are available in the database, but they aren't installed. To install and start
using its features, you need to run the appropriate SQL query in the database. First you
need to connect in
psql
and then run the SQL query to install the extension that you want:
$ heroku pg:psql --app your-app-name
---> Connecting to HEROKU_POSTGRESQL_GOLD_URL (DATABASE_URL)
psql (9.3.4, server 9.3.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
app-name::GOLD=> CREATE EXTENSION extension_name_to_install;
