Contents
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
- PostgreSQL 13
CREATE EXTENSION … FROM …
syntax removed (commit 70a77320)
- PostgreSQL 9.6
CREATE EXTENSION … CASCADE
syntax added (commit b67aaf21)
- PostgreSQL 9.1
- added (commit d9572c4e)
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
References
- PostgreSQL documentation: CREATE EXTENSION