Extensions for PostgreSQL Standalone

PostgreSQL is renowned for its flexibility. You can extend its core functionality with various Extensions.

By default, when you create a database with vDB PostgreSQL, several extensions are already enabled (listed below). You can also manually enable additional extensions as needed.

---

1. Extensions Enabled by Default

For PostgreSQL, newly created databases will have extensions enabled from the template1 database.

After creating a database, you can check the enabled extensions by running:

\dx

or

select * from pg_extension;

Below is the list of extensions enabled by default:

No	Name	Description
1	plpgsql	PL/pgSQL procedural language
2	btree_gin	Support for indexing common datatypes in GIN
3	btree_gist	Support for indexing common datatypes in GiST
4	citext	Data type for case-insensitive character strings
5	cube	Data type for multidimensional cubes
6	dict_int	Text search dictionary template for integers
7	dict_xsyn	Text search dictionary template for extended synonym processing
8	hstore	Data type for storing sets of (key, value) pairs
9	isn	Data types for international product numbering standards
10	lo	Large Object maintenance
11	ltree	Data type for hierarchical tree-like structures
12	pg_trgm	Text similarity measurement and index searching based on trigrams
13	postgis	PostGIS geometry, geography, and raster spatial types and functions
14	postgres_fdw	Foreign-data wrapper for remote PostgreSQL servers
15	unaccent	Text search dictionary that removes accents
16	vector	Vector data type and ivfflat and hnsw access methods

Note: If you want to create a completely blank database, use template0.

CREATE DATABASE dbname TEMPLATE template0;

2. Extensions You Can Enable Manually

You can enable an Extension by running:

CREATE EXTENSION <extension_name>;

Name	Description
fuzzystrmatch	Determine similarities and distance between strings
intarray	Functions, operators, and index support for 1-D arrays of integers
pgcrypto	Cryptographic functions
postgis_tiger_geocoder	PostGIS tiger geocoder and reverse geocoder (requires fuzzystrmatch to be enabled first)
seg	Data type for representing line segments or floating-point intervals
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
uuid-ossp	Generate universally unique identifiers (UUIDs)

---

3. Notes

• The vector extension is only available for vDB instances created from 01/08/2024 onwards.

• To enable it on instances created before that date, please contact GreenNode Cloud Support.