1CREATE EXTENSION(7) PostgreSQL 12.6 Documentation CREATE EXTENSION(7)
2
6 CREATE_EXTENSION - install an extension
7
9 CREATE EXTENSION [ IF NOT EXISTS ] extension_name
10 [ WITH ] [ SCHEMA schema_name ]
11 [ VERSION version ]
12 [ FROM old_version ]
13 [ CASCADE ]
14
16 CREATE EXTENSION loads a new extension into the current database. There
17 must not be an extension of the same name already loaded.
18 Loading an extension essentially amounts to running the extension's
20 script file. The script will typically create new SQL objects such as
21 functions, data types, operators and index support methods. CREATE
22 EXTENSION additionally records the identities of all the created
23 objects, so that they can be dropped again if DROP EXTENSION is issued.
24 Loading an extension requires the same privileges that would be
26 required to create its component objects. For most extensions this
27 means superuser or database owner privileges are needed. The user who
28 runs CREATE EXTENSION becomes the owner of the extension for purposes
29 of later privilege checks, as well as the owner of any objects created
30 by the extension's script.
31
33 IF NOT EXISTS
34 Do not throw an error if an extension with the same name already
35 exists. A notice is issued in this case. Note that there is no
36 guarantee that the existing extension is anything like the one that
37 would have been created from the currently-available script file.
38 extension_name
40 The name of the extension to be installed. PostgreSQL will create
41 the extension using details from the file
42 SHAREDIR/extension/extension_name.control.
43 schema_name
45 The name of the schema in which to install the extension's objects,
46 given that the extension allows its contents to be relocated. The
47 named schema must already exist. If not specified, and the
48 extension's control file does not specify a schema either, the
49 current default object creation schema is used.
50 If the extension specifies a schema parameter in its control file,
52 then that schema cannot be overridden with a SCHEMA clause.
53 Normally, an error will be raised if a SCHEMA clause is given and
54 it conflicts with the extension's schema parameter. However, if the
55 CASCADE clause is also given, then schema_name is ignored when it
56 conflicts. The given schema_name will be used for installation of
57 any needed extensions that do not specify schema in their control
58 files.
59 Remember that the extension itself is not considered to be within
61 any schema: extensions have unqualified names that must be unique
62 database-wide. But objects belonging to the extension can be within
63 schemas.
64 version
66 The version of the extension to install. This can be written as
67 either an identifier or a string literal. The default version is
68 whatever is specified in the extension's control file.
69 old_version
71 FROM old_version must be specified when, and only when, you are
72 attempting to install an extension that replaces an “old style”
73 module that is just a collection of objects not packaged into an
74 extension. This option causes CREATE EXTENSION to run an
75 alternative installation script that absorbs the existing objects
76 into the extension, instead of creating new objects. Be careful
77 that SCHEMA specifies the schema containing these pre-existing
78 objects.
79 The value to use for old_version is determined by the extension's
81 author, and might vary if there is more than one version of the
82 old-style module that can be upgraded into an extension. For the
83 standard additional modules supplied with pre-9.1 PostgreSQL, use
84 unpackaged for old_version when updating a module to extension
85 style.
86 CASCADE
88 Automatically install any extensions that this extension depends on
89 that are not already installed. Their dependencies are likewise
90 automatically installed, recursively. The SCHEMA clause, if given,
91 applies to all extensions that get installed this way. Other
92 options of the statement are not applied to automatically-installed
93 extensions; in particular, their default versions are always
94 selected.
95
97 Before you can use CREATE EXTENSION to load an extension into a
98 database, the extension's supporting files must be installed.
99 Information about installing the extensions supplied with PostgreSQL
100 can be found in Additional Supplied Modules.
101 The extensions currently available for loading can be identified from
103 the pg_available_extensions or pg_available_extension_versions system
104 views.
105 Caution
107 Installing an extension as superuser requires trusting that the
108 extension's author wrote the extension installation script in a
109 secure fashion. It is not terribly difficult for a malicious user
110 to create trojan-horse objects that will compromise later execution
111 of a carelessly-written extension script, allowing that user to
112 acquire superuser privileges. However, trojan-horse objects are
113 only hazardous if they are in the search_path during script
114 execution, meaning that they are in the extension's installation
115 target schema or in the schema of some extension it depends on.
116 Therefore, a good rule of thumb when dealing with extensions whose
117 scripts have not been carefully vetted is to install them only into
118 schemas for which CREATE privilege has not been and will not be
119 granted to any untrusted users. Likewise for any extensions they
120 depend on.
121 The extensions supplied with PostgreSQL are believed to be secure
123 against installation-time attacks of this sort, except for a few
124 that depend on other extensions. As stated in the documentation for
125 those extensions, they should be installed into secure schemas, or
126 installed into the same schemas as the extensions they depend on,
127 or both.
128 For information about writing new extensions, see Section 37.17.
130
132 Install the hstore extension into the current database, placing its
133 objects in schema addons:
134 CREATE EXTENSION hstore SCHEMA addons;
136 Another way to accomplish the same thing:
138 SET search_path = addons;
140 CREATE EXTENSION hstore;
141 Update a pre-9.1 installation of hstore into extension style:
143 CREATE EXTENSION hstore SCHEMA public FROM unpackaged;
145 Be careful to specify the schema in which you installed the existing
147 hstore objects.
148
150 CREATE EXTENSION is a PostgreSQL extension.
151
153 ALTER EXTENSION (ALTER_EXTENSION(7)), DROP EXTENSION
154 (DROP_EXTENSION(7))
155PostgreSQL 12.6 2021 CREATE EXTENSION(7)