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