How to Use the CREATE EXTENSION Command in PostgreSQL

If you’ve spent any real time working with PostgreSQL, you’ve probably noticed that it doesn’t try to do everything out of the box. Instead, it ships with a lean core and lets you bolt on extra functionality only when you actually need it. That’s where the CREATE EXTENSION command comes in. It’s one of those commands that looks simple on the surface but unlocks a huge amount of power once you understand how it works.

In this guide, I’m going to walk through exactly what CREATE EXTENSION does, how the syntax works, what parameters you can pass, and how to use it in real projects. I’ll also cover common problems people run into and how to avoid them.

What Is the CREATE EXTENSION Command?

PostgreSQL extensions are packages of SQL objects — functions, data types, operators, index types, and more — that can be installed into a database as a single unit. Instead of manually creating dozens of functions and types yourself, you install an extension and PostgreSQL handles the wiring for you.

Some extensions are bundled with PostgreSQL itself (these live in the contrib module), while others are third-party packages you install separately on the server before they become available to CREATE EXTENSION. Popular examples include:

• pgcrypto for cryptographic functions
• uuid-ossp for UUID generation
• postgis for geospatial data types and functions
• pg_stat_statements for query performance tracking
• hstore for key-value pair storage
• citext for case-insensitive text

The CREATE EXTENSION command is what actually activates one of these packages inside a specific database. It’s important to understand that installing the extension’s files on the server (via a package manager like apt, yum, or compiling from source) is a separate step from running CREATE EXTENSION in SQL. The SQL command just tells PostgreSQL, “Now register this extension’s objects inside this particular database.”

Basic Syntax

The simplest form of the command looks like this:

CREATE EXTENSION extension_name;

For example, to enable the pgcrypto extension:

CREATE EXTENSION pgcrypto;

That single line creates all the functions, types, and operators that pgcrypto provides, right inside your current database.

The full syntax, with all optional clauses, looks like this:

CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]
              [ VERSION version ]
              [ CASCADE ]

Let’s break each piece down.

Parameters Explained

extension_name

This is the name of the extension you want to install. It must match the name PostgreSQL recognizes, which usually corresponds to a control file on the server (something like pgcrypto.control). You can check what’s available on your server with:

SELECT * FROM pg_available_extensions;

This query returns a list of every extension the server knows about, along with the default version and a short description.

IF NOT EXISTS

Adding IF NOT EXISTS tells PostgreSQL not to throw an error if the extension is already installed. Instead, it just issues a notice and moves on. This is extremely handy in migration scripts or setup automation where the script might run more than once.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Without this clause, running CREATE EXTENSION pgcrypto; a second time will fail with an error saying the extension already exists.

SCHEMA schema_name

By default, an extension’s objects get installed into your current search path’s schema, often public. But you can explicitly choose where the extension’s objects should live using the SCHEMA clause.

CREATE EXTENSION hstore SCHEMA extensions;

This is a good practice in larger databases where you want to keep extension objects separate from your application’s own tables and functions, rather than cluttering the public schema. It also helps avoid naming collisions.

VERSION version

Some extensions have multiple versions available on the server. If you need a specific one instead of the default, you can specify it:

CREATE EXTENSION postgis VERSION '3.3.2';

If you leave this out, PostgreSQL installs whatever version is marked as the default in the extension’s control file.

CASCADE

Some extensions depend on other extensions. For instance, postgis_topology depends on postgis. If you try to install an extension that has unmet dependencies, PostgreSQL will normally throw an error. Adding CASCADE tells PostgreSQL to automatically install any required extensions first.

CREATE EXTENSION postgis_topology CASCADE;

This saves you from manually figuring out and installing the dependency chain yourself.

Practical Examples

Let’s go through some real-world examples you’re likely to use.

Example 1: Enabling UUID Generation

If you want to generate UUIDs as primary keys instead of relying on sequential integers, you’ll typically enable uuid-ossp or use the built-in gen_random_uuid() function from pgcrypto (available since PostgreSQL 13, gen_random_uuid() is also built into core now, but older setups still rely on extensions).

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT NOT NULL UNIQUE
);

Notice the quotes around "uuid-ossp" — because the name contains a hyphen, it must be quoted as a valid identifier.

Example 2: Case-Insensitive Text Columns

Say you’re building a login system and want email addresses to be treated as case-insensitive without writing LOWER() everywhere.

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    email CITEXT UNIQUE NOT NULL
);

Now 'User@Example.com' and 'user@example.com' will be treated as the same value for uniqueness checks and comparisons.

Example 3: Query Performance Monitoring

pg_stat_statements is one of the most useful extensions for diagnosing slow queries in production.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Note that this particular extension also requires a change to postgresql.conf (shared_preload_libraries = 'pg_stat_statements') and a server restart before it becomes fully functional. Running CREATE EXTENSION alone won’t be enough for this one.

Example 4: Geospatial Data with PostGIS

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(Point, 4326)
);

Once installed, postgis gives you spatial data types and hundreds of functions for distance calculations, intersections, and mapping queries.

Example 5: Key-Value Storage with hstore

CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE product_attributes (
    product_id INT,
    attributes HSTORE
);

INSERT INTO product_attributes VALUES (1, 'color=>red, size=>large');

Checking Installed Extensions

Once you’ve created a few extensions, you’ll want a way to verify what’s actually active in your database. Two views are useful here:

-- List extensions installed in the current database
SELECT * FROM pg_extension;

-- List extensions available on the server, installed or not
SELECT * FROM pg_available_extensions;

The difference matters: pg_available_extensions shows everything the server could install, while pg_extension shows what’s actually active in the database you’re connected to.

Updating and Removing Extensions

Extensions can be upgraded to newer versions using ALTER EXTENSION:

ALTER EXTENSION postgis UPDATE TO '3.4.0';

And removed entirely using DROP EXTENSION:

DROP EXTENSION IF EXISTS hstore;

If other objects in your database depend on the extension (like a column using HSTORE as its type), dropping it will fail unless you add CASCADE, which will also drop the dependent objects. Use that carefully — it’s not reversible without a backup.

Common Use Cases

• Cryptography and security: pgcrypto for hashing passwords, encrypting sensitive columns, and generating secure random values.
• Full-text search: extensions like unaccent help normalize search input by stripping accents.
• Foreign data access: postgres_fdw and dblink let you query external databases as if they were local tables.
• Performance tuning: pg_stat_statements and pg_buffercache give visibility into what the database is actually doing.
• Geospatial applications: postgis is close to an industry standard for mapping and location-based apps built on Postgres.
• Custom data types: hstore, citext, and ltree extend what a column can natively store and compare.

Troubleshooting Common Issues

“Extension does not exist” Error

This usually means the extension’s files aren’t installed on the server itself. Running CREATE EXTENSION only registers the extension inside a database — it can’t conjure files that were never installed on the OS level. On Debian/Ubuntu systems, you’d typically install something like:

sudo apt install postgresql-16-postgis-3

Then retry the CREATE EXTENSION command from within psql.

Permission Denied Errors

Creating an extension usually requires superuser privileges, or at least membership in a role with the right permissions, depending on how “trusted” the extension is marked. PostgreSQL has a concept of “trusted extensions” (introduced more fully in PostgreSQL 13) that can be installed by non-superusers who have CREATE privilege on the database. If you’re not a superuser and get a permission error, ask your DBA to either install it for you or grant the appropriate trust settings.

Extension Already Exists

If you see an error that the extension is already created, either use IF NOT EXISTS in your script, or check with pg_extension first to avoid redundant calls.

Version Mismatch After a PostgreSQL Upgrade

After a major PostgreSQL version upgrade, extensions sometimes need to be updated to a version compatible with the new server. Run ALTER EXTENSION extension_name UPDATE; after an upgrade to make sure everything lines up correctly.

Dependency Errors

If an extension depends on another that isn’t installed, you’ll get an error unless you use CASCADE. It’s good practice to check an extension’s documentation before installing to know what it depends on ahead of time, rather than relying on CASCADE blindly in production.

Best Practices

1. Always specify a schema for extensions in larger projects. Keeping extension objects out of public reduces the chance of naming collisions with your own application code.
2. Use IF NOT EXISTS in migration scripts. This makes your deployment scripts idempotent, so re-running them doesn’t break anything.
3. Pin extension versions in production. Don’t let an automatic install grab whatever the “default” version happens to be on a given server; specify the version explicitly so your environments stay consistent.
4. Document why each extension is needed. In a team setting, it helps future developers (and future you) to know why pgcrypto or postgis is part of the schema.
5. Test extension upgrades in a staging environment first. Some extension version bumps introduce breaking changes to function signatures or behavior.
6. Audit installed extensions periodically. Query pg_extension occasionally to make sure you’re not carrying around unused extensions that add attack surface or maintenance overhead.
7. Understand the difference between OS-level installation and SQL-level activation. A lot of confusion around CREATE EXTENSION comes from mixing these two steps up.

Understanding Extension Control Files

Every extension that can be installed with CREATE EXTENSION is backed by a control file on the server, typically named something like extension_name.control, sitting in PostgreSQL’s SHAREDIR/extension directory. This file tells PostgreSQL basic metadata about the extension: its default version, whether it’s relocatable to a different schema, whether it requires other extensions, and a short description.

You can find where your server keeps these files with:

pg_config --sharedir

Then look inside the extension subdirectory. Understanding that this file exists helps demystify a lot of CREATE EXTENSION behavior — for instance, why some extensions can be moved between schemas with ALTER EXTENSION ... SET SCHEMA and others can’t; that’s controlled by the relocatable flag in the control file.

Alongside the control file, you’ll find one or more SQL script files (like pgcrypto--1.3.sql) that contain the actual CREATE FUNCTION, CREATE TYPE, and other statements that get executed when you run CREATE EXTENSION. There are often multiple version-specific scripts, plus “update scripts” (like pgcrypto--1.2--1.3.sql) that PostgreSQL uses internally when you run ALTER EXTENSION ... UPDATE.

Trusted Extensions and Non-Superuser Installation

Historically, installing any extension required superuser privileges, which was a real friction point for managed database services (like cloud-hosted Postgres) where end users don’t get superuser access. PostgreSQL addressed this with the concept of “trusted” extensions.

A trusted extension is one whose control file includes trusted = true, meaning PostgreSQL considers it safe enough that a non-superuser with CREATE privilege on the database can install it without needing elevated access. Extensions like pgcrypto, citext, hstore, uuid-ossp, and pg_stat_statements are commonly marked trusted in modern PostgreSQL versions.

You can check whether an extension is trusted before attempting to install it:

SELECT name, trusted, superuser
FROM pg_available_extensions
WHERE name = 'pgcrypto';

If trusted is true, a regular role with database-level CREATE privilege should be able to run CREATE EXTENSION pgcrypto; without needing to ask a DBA to do it for them. This has made self-service extension management on managed cloud database platforms considerably more practical than it used to be.

Extensions in Managed and Cloud Environments

If you’re running PostgreSQL on a managed service (Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL, and similar), it’s worth knowing that these platforms typically restrict which extensions are available, since they can’t grant true superuser access to customers for security and stability reasons. Each provider maintains its own allow-list of supported extensions.

Before assuming an extension will work on a managed service, check the provider’s documentation for supported extensions, and use pg_available_extensions after connecting to confirm what’s actually offered on that specific instance. Attempting to install an extension the provider hasn’t allow-listed will simply fail, often with a permissions-related error rather than a clear “not supported” message, which can be confusing if you don’t know to check this first.

A Note on Extension Security

Because extensions can introduce new functions, operators, and types with arbitrary behavior, it’s worth thinking about them from a security perspective, not just a functionality one. A malicious or poorly written untrusted extension, if a superuser were to naively install one from an unverified source, could compromise the entire server. This is part of why the trusted/untrusted distinction exists, and why it’s good practice to only install extensions from official PostgreSQL contrib modules or well-known, actively maintained community projects, rather than obscure or unmaintained ones you haven’t vetted.

Foreign Data Wrapper Extensions: A Special Category

Worth calling out separately is a category of extensions known as Foreign Data Wrappers (FDWs), which let PostgreSQL query external data sources as though they were local tables. postgres_fdw is the most common example, letting you query another PostgreSQL database directly:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER remote_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote-host.example.com', port '5432', dbname 'remotedb');

CREATE USER MAPPING FOR CURRENT_USER
    SERVER remote_server
    OPTIONS (user 'remote_user', password 'remote_password');

CREATE FOREIGN TABLE remote_orders (
    id INT,
    customer_id INT,
    amount NUMERIC
) SERVER remote_server OPTIONS (schema_name 'public', table_name 'orders');

SELECT * FROM remote_orders WHERE customer_id = 42;

This pattern — CREATE EXTENSION, then CREATE SERVER, then CREATE USER MAPPING, then CREATE FOREIGN TABLE — is worth recognizing as a distinct workflow from simpler extensions like pgcrypto, since FDWs involve several additional setup steps beyond the initial CREATE EXTENSION call before they’re actually usable.

Other FDWs follow the same general pattern for different external sources: file_fdw for reading flat files as tables, and various third-party FDWs for MySQL, MongoDB, and other systems.

Pinning Extension Versions in Infrastructure-as-Code

For teams managing PostgreSQL schema through migration tools (Flyway, Liquibase, Rails migrations, Django migrations, and similar), it’s worth explicitly pinning extension versions in migration scripts rather than relying on whatever the “default” happens to be at the time a migration runs:

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH VERSION '1.3' SCHEMA extensions;

This matters because different servers, especially across development, staging, and production, might have different extension package versions installed depending on when they were provisioned. Pinning the version explicitly in your migration scripts means a fresh environment provisioned months later ends up with the exact same extension version as everywhere else, rather than silently picking up whatever newer default version happens to be available on that particular server at that particular time. If the specified version isn’t available on a given server, CREATE EXTENSION will fail clearly with a version-not-found error, which is a far better outcome than a silent mismatch causing subtle behavioral differences down the line.

Wrapping Up

The CREATE EXTENSION command is deceptively simple — one line of SQL — but it opens the door to an enormous ecosystem of functionality that PostgreSQL doesn’t ship with by default. Whether you’re adding cryptographic functions, geospatial types, or performance monitoring tools, understanding how this command works, along with its optional clauses like SCHEMA, VERSION, and CASCADE, will save you a lot of headaches down the line.

My advice: get comfortable checking pg_available_extensions and pg_extension regularly, always use IF NOT EXISTS in scripts meant to be re-run, and be deliberate about which schema your extensions live in. Once you’ve got that workflow down, extensions stop being a mystery and start being one of the most useful tools in your PostgreSQL toolbox.