1<!--
2doc/src/sgml/ref/create_extension.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="SQL-CREATEEXTENSION">
7 <indexterm zone="sql-createextension">
8  <primary>CREATE EXTENSION</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>CREATE EXTENSION</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>CREATE EXTENSION</refname>
19  <refpurpose>install an extension</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23<synopsis>
24CREATE EXTENSION [ IF NOT EXISTS ] <replaceable class="parameter">extension_name</replaceable>
25    [ WITH ] [ SCHEMA <replaceable class="parameter">schema_name</replaceable> ]
26             [ VERSION <replaceable class="parameter">version</replaceable> ]
27             [ FROM <replaceable class="parameter">old_version</replaceable> ]
28             [ CASCADE ]
29</synopsis>
30 </refsynopsisdiv>
31
32 <refsect1>
33  <title>Description</title>
34
35  <para>
36   <command>CREATE EXTENSION</command> loads a new extension into the current
37   database.  There must not be an extension of the same name already loaded.
38  </para>
39
40  <para>
41   Loading an extension essentially amounts to running the extension's script
42   file.  The script will typically create new <acronym>SQL</> objects such as
43   functions, data types, operators and index support methods.
44   <command>CREATE EXTENSION</command> additionally records the identities
45   of all the created objects, so that they can be dropped again if
46   <command>DROP EXTENSION</command> is issued.
47  </para>
48
49  <para>
50   Loading an extension requires the same privileges that would be
51   required to create its component objects.  For most extensions this
52   means superuser or database owner privileges are needed.
53   The user who runs <command>CREATE EXTENSION</command> becomes the
54   owner of the extension for purposes of later privilege checks, as well
55   as the owner of any objects created by the extension's script.
56  </para>
57
58 </refsect1>
59
60 <refsect1>
61  <title>Parameters</title>
62
63    <variablelist>
64     <varlistentry>
65      <term><literal>IF NOT EXISTS</></term>
66      <listitem>
67       <para>
68        Do not throw an error if an extension with the same name already
69        exists.  A notice is issued in this case.  Note that there is no
70        guarantee that the existing extension is anything like the one that
71        would have been created from the currently-available script file.
72       </para>
73      </listitem>
74     </varlistentry>
75
76     <varlistentry>
77      <term><replaceable class="parameter">extension_name</replaceable></term>
78      <listitem>
79       <para>
80        The name of the extension to be
81        installed. <productname>PostgreSQL</productname> will create the
82        extension using details from the file
83        <literal>SHAREDIR/extension/</literal><replaceable class="parameter">extension_name</replaceable><literal>.control</literal>.
84       </para>
85      </listitem>
86     </varlistentry>
87
88     <varlistentry>
89      <term><replaceable class="parameter">schema_name</replaceable></term>
90      <listitem>
91       <para>
92        The name of the schema in which to install the extension's
93        objects, given that the extension allows its contents to be
94        relocated.  The named schema must already exist.
95        If not specified, and the extension's control file does not specify a
96        schema either, the current default object creation schema is used.
97       </para>
98
99       <para>
100        If the extension specifies a <literal>schema</> parameter in its
101        control file, then that schema cannot be overridden with
102        a <literal>SCHEMA</> clause.  Normally, an error will be raised if
103        a <literal>SCHEMA</> clause is given and it conflicts with the
104        extension's <literal>schema</> parameter.  However, if
105        the <literal>CASCADE</> clause is also given,
106        then <replaceable class="parameter">schema_name</replaceable> is
107        ignored when it conflicts.  The
108        given <replaceable class="parameter">schema_name</replaceable> will be
109        used for installation of any needed extensions that do not
110        specify <literal>schema</> in their control files.
111       </para>
112
113       <para>
114        Remember that the extension itself is not considered to be within any
115        schema: extensions have unqualified names that must be unique
116        database-wide.  But objects belonging to the extension can be within
117        schemas.
118       </para>
119      </listitem>
120     </varlistentry>
121
122     <varlistentry>
123      <term><replaceable class="parameter">version</replaceable></term>
124      <listitem>
125       <para>
126        The version of the extension to install.  This can be written as
127        either an identifier or a string literal.  The default version is
128        whatever is specified in the extension's control file.
129       </para>
130      </listitem>
131     </varlistentry>
132
133     <varlistentry>
134      <term><replaceable class="parameter">old_version</replaceable></term>
135      <listitem>
136       <para>
137        <literal>FROM</> <replaceable class="parameter">old_version</>
138        must be specified when, and only when, you are attempting to install
139        an extension that replaces an <quote>old style</> module that is just
140        a collection of objects not packaged into an extension.  This option
141        causes <command>CREATE EXTENSION</> to run an alternative installation
142        script that absorbs the existing objects into the extension, instead
143        of creating new objects.  Be careful that <literal>SCHEMA</> specifies
144        the schema containing these pre-existing objects.
145       </para>
146
147       <para>
148        The value to use for <replaceable
149        class="parameter">old_version</replaceable> is determined by the
150        extension's author, and might vary if there is more than one version
151        of the old-style module that can be upgraded into an extension.
152        For the standard additional modules supplied with pre-9.1
153        <productname>PostgreSQL</productname>, use <literal>unpackaged</>
154        for <replaceable class="parameter">old_version</replaceable> when
155        updating a module to extension style.
156       </para>
157      </listitem>
158     </varlistentry>
159
160     <varlistentry>
161      <term><literal>CASCADE</></term>
162      <listitem>
163       <para>
164        Automatically install any extensions that this extension depends on
165        that are not already installed.  Their dependencies are likewise
166        automatically installed, recursively.  The <literal>SCHEMA</> clause,
167        if given, applies to all extensions that get installed this way.
168        Other options of the statement are not applied to
169        automatically-installed extensions; in particular, their default
170        versions are always selected.
171       </para>
172      </listitem>
173     </varlistentry>
174  </variablelist>
175 </refsect1>
176
177 <refsect1>
178  <title>Notes</title>
179
180  <para>
181   Before you can use <command>CREATE EXTENSION</> to load an extension
182   into a database, the extension's supporting files must be installed.
183   Information about installing the extensions supplied with
184   <productname>PostgreSQL</productname> can be found in
185   <link linkend="contrib">Additional Supplied Modules</link>.
186  </para>
187
188  <para>
189   The extensions currently available for loading can be identified from the
190   <link linkend="view-pg-available-extensions"><structname>pg_available_extensions</structname></link>
191   or
192   <link linkend="view-pg-available-extension-versions"><structname>pg_available_extension_versions</structname></link>
193   system views.
194  </para>
195
196  <caution>
197   <para>
198    Installing an extension as superuser requires trusting that the
199    extension's author wrote the extension installation script in a secure
200    fashion.  It is not terribly difficult for a malicious user to create
201    trojan-horse objects that will compromise later execution of a
202    carelessly-written extension script, allowing that user to acquire
203    superuser privileges.  However, trojan-horse objects are only hazardous
204    if they are in the <varname>search_path</varname> during script
205    execution, meaning that they are in the extension's installation target
206    schema or in the schema of some extension it depends on.  Therefore, a
207    good rule of thumb when dealing with extensions whose scripts have not
208    been carefully vetted is to install them only into schemas for which
209    CREATE privilege has not been and will not be granted to any untrusted
210    users.  Likewise for any extensions they depend on.
211   </para>
212
213   <para>
214    The extensions supplied with <productname>PostgreSQL</productname> are
215    believed to be secure against installation-time attacks of this sort,
216    except for a few that depend on other extensions.  As stated in the
217    documentation for those extensions, they should be installed into secure
218    schemas, or installed into the same schemas as the extensions they
219    depend on, or both.
220   </para>
221  </caution>
222
223  <para>
224   For information about writing new extensions, see
225   <xref linkend="extend-extensions">.
226  </para>
227 </refsect1>
228
229 <refsect1>
230  <title>Examples</title>
231
232  <para>
233   Install the <link linkend="hstore">hstore</link> extension into the
234   current database, placing its objects in schema <literal>addons</literal>:
235<programlisting>
236CREATE EXTENSION hstore SCHEMA addons;
237</programlisting>
238   Another way to accomplish the same thing:
239<programlisting>
240SET search_path = addons;
241CREATE EXTENSION hstore;
242</programlisting>
243  </para>
244
245  <para>
246   Update a pre-9.1 installation of <literal>hstore</> into
247   extension style:
248<programlisting>
249CREATE EXTENSION hstore SCHEMA public FROM unpackaged;
250</programlisting>
251   Be careful to specify the schema in which you installed the existing
252   <literal>hstore</> objects.
253  </para>
254 </refsect1>
255
256 <refsect1>
257  <title>Compatibility</title>
258
259  <para>
260   <command>CREATE EXTENSION</command> is a <productname>PostgreSQL</>
261   extension.
262  </para>
263 </refsect1>
264
265 <refsect1>
266  <title>See Also</title>
267
268  <simplelist type="inline">
269   <member><xref linkend="sql-alterextension"></member>
270   <member><xref linkend="sql-dropextension"></member>
271  </simplelist>
272 </refsect1>
273
274</refentry>
275