Skip to content

PostgreSQL EXTENSION

    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 EXTENSION name UPDATE [ TO new_version ]
    ALTER EXTENSION name SET SCHEMA new_schema
    ALTER EXTENSION name ADD member_object
    ALTER EXTENSION name DROP member_object
     
    where member_object is:
     
      ACCESS METHOD object_name |
      AGGREGATE aggregate_name ( aggregate_signature ) |
      CAST (source_type AS target_type) |
      COLLATION object_name |
      CONVERSION object_name |
      DOMAIN object_name |
      EVENT TRIGGER object_name |
      FOREIGN DATA WRAPPER object_name |
      FOREIGN TABLE object_name |
      FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
      MATERIALIZED VIEW object_name |
      OPERATOR operator_name (left_type, right_type) |
      OPERATOR CLASS object_name USING index_method |
      OPERATOR FAMILY object_name USING index_method |
      [ PROCEDURAL ] LANGUAGE object_name |
      PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
      ROUTINE routine_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 FOR type_name LANGUAGE lang_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: INOUTINOUT, 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 ININOUT, 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.