Supported PostgreSQL Extensions

A quick reference guide to all PostgreSQL extensions supported by Triggr Databases.

PostgreSQL is an open-source, object-relational database management system renowned for its extensibility, data integrity, and performance. The system maintains full ACID (Atomicity, Consistency, Isolation, Durability) compliance through its concurrency support and enables customisation through dynamic loading and catalogue-driven operations.

Extensions are pre-packaged collections of SQL objects that enhance PostgreSQL functionality, allowing users to add specialised features to their database without modifying the core system.

Managing PostgreSQL Extensions

Before running the commands below, log in to your PostgreSQL database using psql:

psql -h <host> -p <port> -U <username> -d <database_name>

Replace the placeholder values above with your actual database connection details.

Once connected, you can manage PostgreSQL extensions in Triggr Databases using the following commands:

• Installing supported extensions command:

  CREATE EXTENSION <extension-name>;

• Upgrading extensions command:

  ALTER EXTENSION <extension-name> UPDATE;

• Disable extensions command:

  DROP EXTENSION <extension-name>;

• List currently installed extensions command:

  \dx

Available Extensions

To see what extensions are available in your database, you can run this command from the command line using psql:

SELECT * FROM pg_available_extensions;

View Available Extensions - Example

You can find your connection details on your Triggr Database dashboard:

Connections tab of a PostgreSQL database deployed on Triggr Database

Example with sample values:

Log in using psql by running this command:

psql -h 78.47.225.173 -p 14295 -U triggradmin -d postgres

Once you are connected, run the command:

SELECT * FROM pg_available_extensions;

You will see an output like the one below

             name             | default_version | installed_version |                                                       comment                                     

------------------------------+-----------------+-------------------+---------------------------------------------------------------------------------------------------------------------
 sslinfo                      | 1.2             |                   | information about SSL certificates
 cube                         | 1.5             |                   | data type for multidimensional cubes
 citext                       | 1.6             |                   | data type for case-insensitive character strings
 seg                          | 1.4             |                   | data type for representing line segments or floating-point intervals
 btree_gist                   | 1.7             |                   | support for indexing common datatypes in GiST
 plpgsql                      | 1.0             | 1.0               | PL/pgSQL procedural language
 xml2                         | 1.1             |                   | XPath querying and XSLT
 insert_username              | 1.0             |                   | functions for tracking who changed a table

Triggr PostgreSQL Database comes with support for the PostgreSQL extensions listed below.

Extension Name	Description
address_standardizer	Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
address_standardizer_data_us	Address Standardizer US dataset example
adminpack	administrative functions for PostgreSQL
amcheck	functions for verifying relation integrity
autoinc	functions for autoincrementing fields
bloom	bloom access method - signature file based index
btree_gin	support for indexing common datatypes in GIN
btree_gist	support for indexing common datatypes in GiST
citext	data type for case-insensitive character strings
cube	data type for multidimensional cubes
dblink	connect to other PostgreSQL databases from within a database
dict_int	text search dictionary template for integers
dict_xsyn	text search dictionary template for extended synonym processing
earthdistance	calculate great-circle distances on the surface of the Earth
file_fdw	foreign-data wrapper for flat file access
fuzzystrmatch	determine similarities and distance between strings
hstore	data type for storing sets of (key, value) pairs
insert_username	functions for tracking who changed a table
intagg	integer aggregator and enumerator (obsolete)
intarray	functions, operators, and index support for 1-D arrays of integers
isn	data types for international product numbering standards
lo	Large Object maintenance
ltree	data type for hierarchical tree-like structures
moddatetime	functions for tracking last modification time
old_snapshot	utilities in support of old_snapshot_threshold
orafce	Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
pageinspect	inspect the contents of database pages at a low level
pg_buffercache	examine the shared buffer cache
pg_freespacemap	examine the free space map (FSM)
pg_prewarm	prewarm relation data
pg_stat_statements	track planning and execution statistics of all SQL statements executed
pg_surgery	extension to perform surgery on a damaged relation
pg_trgm	text similarity measurement and index searching based on trigrams
pg_visibility	examine the visibility map (VM) and page-level visibility info
pg_walinspect	functions to inspect contents of PostgreSQL Write-Ahead Log
pgaudit	provides auditing functionality
pgautofailover	pg_auto_failover
pgcrypto	cryptographic functions
pgrowlocks	show row-level locking information
pgstattuple	show tuple-level statistics
pljava	PL/Java procedural language (https://tada.github.io/pljava/)
plpgsql	PL/pgSQL procedural language
postgis	PostGIS geometry and geography spatial types and functions
postgis_raster	PostGIS raster types and functions
postgis_tiger_geocoder	PostGIS tiger geocoder and reverse geocoder
postgis_topology	PostGIS topology spatial types and functions
postgres_fdw	foreign-data wrapper for remote PostgreSQL servers
refint	functions for implementing referential integrity (obsolete)
seg	data type for representing line segments or floating-point intervals
sslinfo	information about SSL certificates
tablefunc	functions that manipulate whole tables, including crosstab
tcn	Triggered change notifications
tsm_system_rows	TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time	TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent	text search dictionary that removes accents
uuid-ossp	generate universally unique identifiers (UUIDs)
xml2	XPath querying and XSLT