Postgres extensions

Beta

Lakebase Postgres (Autoscaling Beta) is the next version of Lakebase, available for evaluation only. For production workloads, use Lakebase Public Preview. See choosing between versions to understand which version is right for you.

Lakebase provides support for Postgres extensions, enabling you to extend your database functionality with additional features and capabilities. See the Install an extension section below for extension installation instructions.

Extension	Description	Version	Documentation
address_standardizer	Parse an address into constituent elements for geocoding address normalization	3.3.3	PostGIS Extras
address_standardizer_data_us	Address Standardizer US dataset example	3.3.3	PostGIS Extras
autoinc	Functions for autoincrementing fields	1	PostgreSQL autoinc
bloom	Bloom access method - signature file based index	1	PostgreSQL bloom
btree_gin	Support for indexing common datatypes in GIN	1.3	PostgreSQL btree_gin
btree_gist	Support for indexing common datatypes in GiST	1.7	PostgreSQL btree_gist
citext	Data type for case-insensitive character strings	1.6	PostgreSQL citext
cube	Data type for multidimensional cubes	1.5	PostgreSQL cube
dict_int	Text search dictionary template for integers	1	PostgreSQL dict_int
earthdistance	Calculate great-circle distances on the surface of the Earth	1.2	PostgreSQL earthdistance
fuzzystrmatch	Determine similarities and distance between strings	1.2	PostgreSQL fuzzystrmatch
hll	Type for storing hyperloglog data	2.18	PostgreSQL HLL
hstore	Data type for storing sets of (key, value) pairs	1.8	PostgreSQL hstore
insert_username	Functions for tracking who changed a table	1	PostgreSQL insert_username
intagg	Integer aggregator and enumerator (obsolete)	1.1	PostgreSQL intagg
intarray	Functions, operators, and index support for 1-D arrays of integers	1.5	PostgreSQL intarray
isn	Data types for international product numbering standards	1.2	PostgreSQL isn
lo	Large Object maintenance	1.1	PostgreSQL lo
ltree	Data type for hierarchical tree-like structures	1.2	PostgreSQL ltree
moddatetime	Functions for tracking last modification time	1	PostgreSQL moddatetime
pg_graphql	GraphQL support	1.5.9	pg_graphql
pg_jsonschema	JSON schema validation	0.3.3	pg_jsonschema
pg_prewarm	Prewarm relation data	1.2	PostgreSQL pg_prewarm
pg_stat_statements	Track planning and execution statistics of all SQL statements executed	1.10	PostgreSQL pg_stat_statements
pg_trgm	Text similarity measurement and index searching based on trigrams	1.6	PostgreSQL pg_trgm
pgcrypto	Cryptographic functions	1.3	PostgreSQL pgcrypto
pgrowlocks	Show row-level locking information	1.2	PostgreSQL pgrowlocks
plpgsql	PL/pgSQL procedural language	1	PostgreSQL PL/pgSQL
postgis	PostGIS geometry and geography spatial types and functions	3.3.3	PostGIS
postgis_raster	PostGIS raster types and functions	3.3.3	PostGIS Raster
postgis_sfcgal	PostGIS SFCGAL functions	3.3.3	PostGIS SFCGAL
postgis_tiger_geocoder	PostGIS tiger geocoder and reverse geocoder	3.3.3	PostGIS Tiger Geocoder
postgis_topology	PostGIS topology spatial types and functions	3.3.3	PostGIS Topology
refint	Functions for implementing referential integrity (obsolete)	1	PostgreSQL refint
seg	Data type for representing line segments or floating-point intervals	1.4	PostgreSQL seg
tablefunc	Functions that manipulate whole tables, including crosstab	1	PostgreSQL tablefunc
tcn	Triggered change notifications	1	PostgreSQL tcn
tsm_system_rows	TABLESAMPLE method which accepts number of rows as a limit	1	PostgreSQL tsm_system_rows
tsm_system_time	TABLESAMPLE method which accepts time in milliseconds as a limit	1	PostgreSQL tsm_system_time
unaccent	Text search dictionary that removes accents	1.1	PostgreSQL unaccent
uuid-ossp	Generate universally unique identifiers (UUIDs)	1.1	PostgreSQL uuid-ossp
vector	Vector data type and ivfflat and hnsw access methods	0.8.0	pgvector
xml2	XPath querying and XSLT	1.1	PostgreSQL xml2

Query available extensions

To see all extensions available in your database, including those in the table above, run:

SQL

SELECT * FROM pg_available_extensions ORDER BY name;

note

Extensions that appear in the results but not in the table above are installed for system use.

Check installed extensions

To view the extensions currently installed in your database and their versions, run:

SQL

SELECT * FROM pg_extension;

Install an extension

Unless otherwise noted, supported extensions can be installed using CREATE EXTENSION syntax.

SQL

CREATE EXTENSION <extension_name>;

Install extensions with dependencies

Some extensions depend on other extensions. Use CASCADE to automatically install required dependencies. For example, PostGIS-related extensions require the base postgis extension:

SQL

CREATE EXTENSION postgis_topology CASCADE;

This installs postgis_topology and automatically installs postgis if it's not already installed.

Install extensions safely

Use IF NOT EXISTS to avoid errors if the extension is already installed:

SQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

You can combine both options:

SQL

CREATE EXTENSION IF NOT EXISTS postgis_raster CASCADE;

Installation locations

You can install extensions from the Lakebase SQL Editor or from a client such as psql that permits running SQL queries. For information about using the Lakebase SQL Editor, see Query with Lakebase SQL Editor. For information about connecting with Postgres clients, see Connect to your database project.