> For the complete documentation index, see [llms.txt](https://docs.clouddley.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.clouddley.com/reference/databases/supported-postgresql-extensions.md).

# Supported PostgreSQL Extensions

[PostgreSQL](https://www.postgresql.org/) 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.

{% hint style="info" %}
[Extensions](https://www.postgresql.org/docs/current/extend-extensions.html) 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.
{% endhint %}

### 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>
```

{% hint style="info" %}
Replace the placeholder values above with your actual database connection details.
{% endhint %}

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

* Installing supported extensions command:<br>

  ```bash
  CREATE EXTENSION <extension-name>;
  ```

* Upgrading extensions command:<br>

  ```bash
  ALTER EXTENSION <extension-name> UPDATE;
  ```

* Disable extensions command:<br>

  <pre class="language-bash"><code class="lang-bash"><strong>DROP EXTENSION &#x3C;extension-name>;
  </strong></code></pre>

* List currently installed extensions command:<br>

  ```bash
  \dx
  ```

### Available Extensions

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

```bash
SELECT * FROM pg_available_extensions;
```

<details>

<summary>View Available Extensions - Example</summary>

You can find your connection details on your  Database dashboard:

<figure><img src="/files/64sdHtbBu4LcLENdzPH6" alt="An image of the connections section of the PostgreSQL database deployed on Clouddley."><figcaption><p>Connections tab of a PostgreSQL database deployed on Clouddley</p></figcaption></figure>

**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
```

</details>

Clouddley PostgreSQL Database comes with support for the PostgreSQL extensions listed below.&#x20;

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