CREATE EXTENSION

A DDL command to create an extension

CREATE EXTENSION is a DDL command for creating an extension.

CREATE EXTENSION was added in PostgreSQL 9.1.

Usage

CREATE EXTENSION creates an extension by reading the matching extension control file and creating the database objects from the extension SQL file or files.

If no matching extension is found (because the extension was not installed or does not exist), CREATE EXTENSION will report that the extension's control file does not exist.

If an extension cannot be found, and PostgreSQL was installed from system packages, it may be necessary to install the appropriate contrib package.

Change history

Examples

Simplest variant:

postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

Extension creation in a user-specified schema:

postgres=# CREATE EXTENSION pg_stat_statements WITH SCHEMA pgss;
CREATE EXTENSION

Extension creation in a user-specified schema is not possible with some extensions:

postgres=# CREATE EXTENSION adminpack WITH SCHEMA public;
ERROR:  extension "adminpack" must be installed in schema "pg_catalog"

Create an extension and any dependencies:

postgres=# CREATE EXTENSION bool_plperlu CASCADE;
NOTICE:  installing required extension "plperlu"
CREATE EXTENSION

Attempting to create a non-existent extension:

postgres=# CREATE EXTENSION foo;
ERROR:  could not open extension control file "/usr/pgsql-11/share/extension/foo.control": No such file or directory

Categories

DDL, Extension, SQL command

See also

ALTER EXTENSION, DROP EXTENSION, extension, pg_extension, pg_available_extensions