1CREATE EXTENSION(7) PostgreSQL 16.1 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 [ CASCADE ]
13
15 CREATE EXTENSION loads a new extension into the current database. There
16 must not be an extension of the same name already loaded.
17 Loading an extension essentially amounts to running the extension's
19 script file. The script will typically create new SQL objects such as
20 functions, data types, operators and index support methods. CREATE
21 EXTENSION additionally records the identities of all the created
22 objects, so that they can be dropped again if DROP EXTENSION is issued.
23 The user who runs CREATE EXTENSION becomes the owner of the extension
25 for purposes of later privilege checks, and normally also becomes the
26 owner of any objects created by the extension's script.
27 Loading an extension ordinarily requires the same privileges that would
29 be required to create its component objects. For many extensions this
30 means superuser privileges are needed. However, if the extension is
31 marked trusted in its control file, then it can be installed by any
32 user who has CREATE privilege on the current database. In this case the
33 extension object itself will be owned by the calling user, but the
34 contained objects will be owned by the bootstrap superuser (unless the
35 extension's script explicitly assigns them to the calling user). This
36 configuration gives the calling user the right to drop the extension,
37 but not to modify individual objects within it.
38
40 IF NOT EXISTS
41 Do not throw an error if an extension with the same name already
42 exists. A notice is issued in this case. Note that there is no
43 guarantee that the existing extension is anything like the one that
44 would have been created from the currently-available script file.
45 extension_name
47 The name of the extension to be installed. PostgreSQL will create
48 the extension using details from the file
49 SHAREDIR/extension/extension_name.control.
50 schema_name
52 The name of the schema in which to install the extension's objects,
53 given that the extension allows its contents to be relocated. The
54 named schema must already exist. If not specified, and the
55 extension's control file does not specify a schema either, the
56 current default object creation schema is used.
57 If the extension specifies a schema parameter in its control file,
59 then that schema cannot be overridden with a SCHEMA clause.
60 Normally, an error will be raised if a SCHEMA clause is given and
61 it conflicts with the extension's schema parameter. However, if the
62 CASCADE clause is also given, then schema_name is ignored when it
63 conflicts. The given schema_name will be used for installation of
64 any needed extensions that do not specify schema in their control
65 files.
66 Remember that the extension itself is not considered to be within
68 any schema: extensions have unqualified names that must be unique
69 database-wide. But objects belonging to the extension can be within
70 schemas.
71 version
73 The version of the extension to install. This can be written as
74 either an identifier or a string literal. The default version is
75 whatever is specified in the extension's control file.
76 CASCADE
78 Automatically install any extensions that this extension depends on
79 that are not already installed. Their dependencies are likewise
80 automatically installed, recursively. The SCHEMA clause, if given,
81 applies to all extensions that get installed this way. Other
82 options of the statement are not applied to automatically-installed
83 extensions; in particular, their default versions are always
84 selected.
85
87 Before you can use CREATE EXTENSION to load an extension into a
88 database, the extension's supporting files must be installed.
89 Information about installing the extensions supplied with PostgreSQL
90 can be found in Additional Supplied Modules.
91 The extensions currently available for loading can be identified from
93 the pg_available_extensions or pg_available_extension_versions system
94 views.
95 Caution
97 Installing an extension as superuser requires trusting that the
98 extension's author wrote the extension installation script in a
99 secure fashion. It is not terribly difficult for a malicious user
100 to create trojan-horse objects that will compromise later execution
101 of a carelessly-written extension script, allowing that user to
102 acquire superuser privileges. However, trojan-horse objects are
103 only hazardous if they are in the search_path during script
104 execution, meaning that they are in the extension's installation
105 target schema or in the schema of some extension it depends on.
106 Therefore, a good rule of thumb when dealing with extensions whose
107 scripts have not been carefully vetted is to install them only into
108 schemas for which CREATE privilege has not been and will not be
109 granted to any untrusted users. Likewise for any extensions they
110 depend on.
111 The extensions supplied with PostgreSQL are believed to be secure
113 against installation-time attacks of this sort, except for a few
114 that depend on other extensions. As stated in the documentation for
115 those extensions, they should be installed into secure schemas, or
116 installed into the same schemas as the extensions they depend on,
117 or both.
118 For information about writing new extensions, see Section 38.17.
120
122 Install the hstore extension into the current database, placing its
123 objects in schema addons:
124 CREATE EXTENSION hstore SCHEMA addons;
126 Another way to accomplish the same thing:
128 SET search_path = addons;
130 CREATE EXTENSION hstore;
131
133 CREATE EXTENSION is a PostgreSQL extension.
134
136 ALTER EXTENSION (ALTER_EXTENSION(7)), DROP EXTENSION
137 (DROP_EXTENSION(7))
138PostgreSQL 16.1 2023 CREATE EXTENSION(7)