Installing and Using PostgreSQL Extensions

Learn about enabling an extension and listing the extensions.

We'll cover the following

PostgreSQL extensions live in a given database, even when their deployment includes shared object libraries that are usually system-wide. Depending on your operating system, a shared object might be a .so, .dll, or .dylib file.

Enable the extension

Once the support files for an extension are deployed at the right place on your operating system, we can type the following SQL command to enable the trigram extension in the current database we’re connected to:

create extension pg_trgm;

Installing the support files for an extension is done via installing the proper package for your operating system. When using Debian, make sure to check out the PostgreSQL Debian distribution at http://apt.postgresql.org.

Dependency

To make pg_trgm installable in PostgreSQL, we have to install the proper contrib package, which is easily done in Debian, as in the following example where we’re targeting PostgreSQL version 10:

$ sudo apt-get install postgresql-contrib-10

Listing the extensions

It’s possible to check whether an extension has already been made available to your PostgreSQL instance with the following SQL query:

table pg_available_extensions;

Here’s an example list:

      name       │ default_version │ installed_version │              comment               
═════════════════╪═════════════════╪═══════════════════╪════════════════════════════════════
 pg_prewarm      │ 1.1             │ ¤                 │ prewarm relation data
 pgcrypto        │ 1.3             │ ¤                 │ cryptographic functions
 lo              │ 1.1             │ ¤                 │ Large Object maintenance
 plperl          │ 1.0             │ ¤                 │ PL/Perl procedural language
 pgstattuple     │ 1.5             │ ¤                 │ show tuple-level statistics
 plpgsql         │ 1.0             │ 1.0               │ PL/pgSQL procedural language
 tcn             │ 1.0             │ ¤                 │ Triggered change notifications
 pg_buffercache  │ 1.3             │ ¤                 │ examine the shared buffer cache
 pg_freespacemap │ 1.2             │ ¤                 │ examine the free space map (FSM)
 sslinfo         │ 1.2             │ ¤                 │ information about SSL certificates
...

Try it yourself

The service for PostgreSQL has been started in the playground. We’ll perform two tasks in the following playground.

  1. Enable the extension using the following syntax:

Get hands-on with 1400+ tech skills courses.