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.
- Enable the extension using the following syntax:
Get hands-on with 1200+ tech skills courses.