CREATE EXTENSION
CREATE EXTENSION — install an extension
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
[ WITH ] [ SCHEMA schema_name
]
[ VERSION version
]
[ CASCADE ]
CREATE EXTENSION
loads a new extension into the current database. There must not be an extension of the same name already loaded.
Loading an extension essentially amounts to running the extension’s script file. The script will typically create new SQL objects such as functions, data types, operators and index support methods. CREATE EXTENSION
additionally records the identities of all the created objects, so that they can be dropped again if DROP EXTENSION
is issued.
The user who runs CREATE EXTENSION
becomes the owner of the extension for purposes of later privilege checks, and normally also becomes the owner of any objects created by the extension’s script.
Loading an extension ordinarily requires the same privileges that would be required to create its component objects. For many extensions this means superuser privileges are needed. However, if the extension is marked trusted in its control file, then it can be installed by any user who has CREATE
privilege on the current database. In this case the extension object itself will be owned by the calling user, but the contained objects will be owned by the bootstrap superuser (unless the extension’s script explicitly assigns them to the calling user). This configuration gives the calling user the right to drop the extension, but not to modify individual objects within it.
Parameters
IF NOT EXISTS
Do not throw an error if an extension with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing extension is anything like the one that would have been created from the currently-available script file.
extension_name
The name of the extension to be installed. PostgreSQL will create the extension using details from the file SHAREDIR/extension/extension_name.control
.
schema_name
The name of the schema in which to install the extension’s objects, given that the extension allows its contents to be relocated. The named schema must already exist. If not specified, and the extension’s control file does not specify a schema either, the current default object creation schema is used.
If the extension specifies a schema
parameter in its control file, then that schema cannot be overridden with a SCHEMA
clause. Normally, an error will be raised if a SCHEMA
clause is given and it conflicts with the extension’s schema
parameter. However, if the CASCADE
clause is also given, then schema_name
is ignored when it conflicts. The given schema_name
will be used for installation of any needed extensions that do not specify schema
in their control files.
Remember that the extension itself is not considered to be within any schema: extensions have unqualified names that must be unique database-wide. But objects belonging to the extension can be within schemas.
version
The version of the extension to install. This can be written as either an identifier or a string literal. The default version is whatever is specified in the extension’s control file.
CASCADE
Automatically install any extensions that this extension depends on that are not already installed. Their dependencies are likewise automatically installed, recursively. The SCHEMA
clause, if given, applies to all extensions that get installed this way. Other options of the statement are not applied to automatically-installed extensions; in particular, their default versions are always selected.
Before you can use CREATE EXTENSION
to load an extension into a database, the extension’s supporting files must be installed.
The extensions currently available for loading can be identified from the pg_available_extensions
or pg_available_extension_versions
system views.
Examples
Install the hstore extension into the current database, placing its objects in schema addons
:
CREATE EXTENSION hstore SCHEMA addons;
Another way to accomplish the same thing:
SET search_path = addons;
CREATE EXTENSION hstore;
ALTER EXTENSION
ALTER EXTENSION — change the definition of an extension
ALTER EXTENSIONname
UPDATE [ TOnew_version
]
ALTER EXTENSIONname
SET SCHEMAnew_schema
ALTER EXTENSIONname
ADDmember_object
ALTER EXTENSIONname
DROPmember_object
where member_object
is:
ACCESS METHOD object_name
|
AGGREGATEaggregate_name
(aggregate_signature
) |
CAST (source_type
AStarget_type
) |
COLLATION object_name
|
CONVERSION object_name
|
DOMAIN object_name
|
EVENT TRIGGER object_name
|
FOREIGN DATA WRAPPER object_name
|
FOREIGN TABLE object_name
|
FUNCTIONfunction_name
[ ( [ [argmode
] [argname
]argtype
[, ...] ] ) ] |
MATERIALIZED VIEW object_name
|
OPERATORoperator_name
(left_type
,right_type
) |
OPERATOR CLASSobject_name
USINGindex_method
|
OPERATOR FAMILYobject_name
USINGindex_method
|
[ PROCEDURAL ] LANGUAGE object_name
|
PROCEDUREprocedure_name
[ ( [ [argmode
] [argname
]argtype
[, ...] ] ) ] |
ROUTINEroutine_name
[ ( [ [argmode
] [argname
]argtype
[, ...] ] ) ] |
SCHEMA object_name
|
SEQUENCE object_name
|
SERVER object_name
|
TABLE object_name
|
TEXT SEARCH CONFIGURATION object_name
|
TEXT SEARCH DICTIONARY object_name
|
TEXT SEARCH PARSER object_name
|
TEXT SEARCH TEMPLATE object_name
|
TRANSFORM FORtype_name
LANGUAGElang_name
|
TYPE object_name
|
VIEW object_name
and aggregate_signature
is:
* |
[argmode
] [argname
]argtype
[ , ... ] |
[ [argmode
] [argname
]argtype
[ , ... ] ] ORDER BY [argmode
] [argname
]argtype
[ , ... ]
ALTER EXTENSION
changes the definition of an installed extension. There are several subforms:
UPDATE
This form updates the extension to a newer version. The extension must supply a suitable update script (or series of scripts) that can modify the currently-installed version into the requested version.
SET SCHEMA
This form moves the extension’s objects into another schema. The extension has to be relocatable for this command to succeed.
ADD member_object
This form adds an existing object to the extension. This is mainly useful in extension update scripts. The object will subsequently be treated as a member of the extension; notably, it can only be dropped by dropping the extension.
DROP member_object
This form removes a member object from the extension. This is mainly useful in extension update scripts. The object is not dropped, only disassociated from the extension.
You must own the extension to use ALTER EXTENSION
. The ADD
/DROP
forms require ownership of the added/dropped object as well.
Parameters
name
The name of an installed extension.
new_version
The desired new version of the extension. This can be written as either an identifier or a string literal. If not specified, ALTER EXTENSION UPDATE
attempts to update to whatever is shown as the default version in the extension’s control file.
new_schema
The new schema for the extension.
object_name
aggregate_name
function_name
operator_name
procedure_name
routine_name
The name of an object to be added to or removed from the extension. Names of tables, aggregates, domains, foreign tables, functions, operators, operator classes, operator families, procedures, routines, sequences, text search objects, types, and views can be schema-qualified.
source_type
The name of the source data type of the cast.
target_type
The name of the target data type of the cast.
argmode
The mode of a function, procedure, or aggregate argument: IN
, OUT
, INOUT
, or VARIADIC
. If omitted, the default is IN
. Note that ALTER EXTENSION
does not actually pay any attention to OUT
arguments, since only the input arguments are needed to determine the function’s identity. So it is sufficient to list the IN
, INOUT
, and VARIADIC
arguments.
argname
The name of a function, procedure, or aggregate argument. Note that ALTER EXTENSION
does not actually pay any attention to argument names, since only the argument data types are needed to determine the function’s identity.
argtype
The data type of a function, procedure, or aggregate argument.
left_type
right_type
The data type(s) of the operator’s arguments (optionally schema-qualified). Write NONE
for the missing argument of a prefix or postfix operator.
PROCEDURAL
This is a noise word.
type_name
The name of the data type of the transform.
lang_name
The name of the language of the transform.
Examples
To update the hstore
extension to version 2.0:
ALTER EXTENSION hstore UPDATE TO '2.0';
To change the schema of the hstore
extension to utils
:
ALTER EXTENSION hstore SET SCHEMA utils;
To add an existing function to the hstore
extension:
ALTER EXTENSION hstore ADD FUNCTION populate_record(anyelement, hstore);
DROP EXTENSION
DROP EXTENSION — remove an extension
DROP EXTENSION [ IF EXISTS ] name
[, ...] [ CASCADE | RESTRICT ]
DROP EXTENSION
removes extensions from the database. Dropping an extension causes its component objects to be dropped as well.
You must own the extension to use DROP EXTENSION
.
Parameters
IF EXISTS
Do not throw an error if the extension does not exist. A notice is issued in this case.
name
The name of an installed extension.
CASCADE
Automatically drop objects that depend on the extension, and in turn all objects that depend on those objects.
RESTRICT
Refuse to drop the extension if any objects depend on it (other than its own member objects and other extensions listed in the same DROP
command). This is the default.
Examples
To remove the extension hstore
from the current database:
DROP EXTENSION hstore;
This command will fail if any of hstore
‘s objects are in use in the database, for example if any tables have columns of the hstore
type. Add the CASCADE
option to forcibly remove those dependent objects as well.