SQL EXTENSION

What is the SQL EXTENSION keyword?

EXTENSION loads or manages a packaged set of SQL objects such as functions, types, or operators within a database.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL EXTENSION: Supported: PostgreSQL 9.1+, Amazon Redshift (limited), CockroachDB, YugabyteDB Not Supported: MySQL, MariaDB, SQL Server, Oracle, SQLite

SQL EXTENSION Full Explanation

In PostgreSQL, the keyword EXTENSION is used inside the commands CREATE EXTENSION, ALTER EXTENSION, and DROP EXTENSION to install, upgrade, or remove a self-contained bundle of SQL objects. An extension encapsulates database functionality (for example, PostGIS, pgcrypto, or hstore) so that it can be versioned, upgraded, and removed as a single unit. When you run CREATE EXTENSION, PostgreSQL looks for a corresponding control file in the shared extension directory, executes the associated SQL script, and records the installed version in pg_extension. ALTER EXTENSION handles upgrades or configuration changes, while DROP EXTENSION removes all objects that belong to the extension in a single transaction. Extensions improve maintainability by isolating vendor-supplied code from user code, enable easier migrations, and support dependency management. The keyword is not part of the ANSI SQL standard and is mainly available in PostgreSQL-compatible systems. Attempting to use it in MySQL, SQL Server, Oracle, or SQLite will produce syntax errors.

SQL EXTENSION Syntax

-- create an extension
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ]
    [ SCHEMA target_schema ]
    [ VERSION version ]
    [ CASCADE ];

-- update an extension
ALTER EXTENSION extension_name UPDATE [ TO version ];

-- remove an extension
DROP EXTENSION [ IF EXISTS ] extension_name [ CASCADE ];

SQL EXTENSION Parameters

extension_name (text) - Name of the extension to manage
IF NOT EXISTS / IF EXISTS (clause) - Optional guard to avoid errors when the extension already exists or is absent
SCHEMA (identifier) - Target schema in which to install the extension objects
VERSION (text) - Specific version to install or update to; omit for default
CASCADE (clause) - Automatically drops objects that depend on the extension when removing

Example Queries Using SQL EXTENSION

-- Install hstore in the public schema
CREATE EXTENSION IF NOT EXISTS hstore;

-- Install PostGIS into a separate schema
CREATE SCHEMA gis;
CREATE EXTENSION postgis WITH SCHEMA gis;

-- Upgrade pgcrypto to a newer version
ALTER EXTENSION pgcrypto UPDATE TO '1.3';

-- Remove an extension and its dependent objects
DROP EXTENSION IF EXISTS fuzzystrmatch CASCADE;

Expected Output Using SQL EXTENSION

Each statement runs in its own transaction
CREATE EXTENSION adds rows to pg_extension and creates all packaged objects
ALTER EXTENSION updates the catalog entry and replaces or alters objects per the upgrade script
DROP EXTENSION deletes the extension entry and all contained objects; dependent objects are also dropped if CASCADE is used

Use Cases with SQL EXTENSION

Installing community or vendor modules such as PostGIS or pg_trgm
Version-controlling database features across environments
Simplifying deployment scripts by bundling SQL objects
Removing all objects belonging to a feature without manual cleanup

Common Mistakes with SQL EXTENSION

Running CREATE EXTENSION without the required superuser or CREATE privilege on the database
Forgetting to include IF NOT EXISTS, leading to errors in idempotent scripts
Assuming EXTENSION works in non-PostgreSQL systems
Omitting CASCADE when dropping an extension that has dependent objects, causing the statement to fail

First Introduced In

PostgreSQL 9.1 (2011)

Frequently Asked Questions

What privileges are required to create an extension?

You must either be a superuser or have the CREATE privilege on the current database plus USAGE on the target schema.

Can I install an extension into a specific schema?

Yes. Use the WITH SCHEMA clause in CREATE EXTENSION to place all objects inside that schema, keeping the extension isolated from public.

How do I list installed extensions?

Query the catalog view pg_extension or run `\dx` in psql to see name, version, and schema for each installed extension.

Is EXTENSION part of the SQL standard?

No. EXTENSION is PostgreSQL specific. Other RDBMS products implement similar functionality through different mechanisms or not at all.