1<!--
2doc/src/sgml/ref/alter_database.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="SQL-ALTERDATABASE">
7 <indexterm zone="sql-alterdatabase">
8  <primary>ALTER DATABASE</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>ALTER DATABASE</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>ALTER DATABASE</refname>
19  <refpurpose>change a database</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23<synopsis>
24ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ] ]
25
26<phrase>where <replaceable class="PARAMETER">option</replaceable> can be:</phrase>
27
28    ALLOW_CONNECTIONS <replaceable class="PARAMETER">allowconn</replaceable>
29    CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
30    IS_TEMPLATE <replaceable class="PARAMETER">istemplate</replaceable>
31
32ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
33
34ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_USER | SESSION_USER }
35
36ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
37
38ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
39ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
40ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>configuration_parameter</replaceable>
41ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET ALL
42</synopsis>
43 </refsynopsisdiv>
44
45 <refsect1>
46  <title>Description</title>
47
48  <para>
49   <command>ALTER DATABASE</command> changes the attributes
50   of a database.
51  </para>
52
53  <para>
54   The first form changes certain per-database settings.  (See below for
55   details.)  Only the database owner or a superuser can change these settings.
56  </para>
57
58  <para>
59   The second form changes the name of the database.  Only the database
60   owner or a superuser can rename a database; non-superuser owners must
61   also have the
62   <literal>CREATEDB</literal> privilege.  The current database cannot
63   be renamed.  (Connect to a different database if you need to do
64   that.)
65  </para>
66
67  <para>
68   The third form changes the owner of the database.
69   To alter the owner, you must own the database and also be a direct or
70   indirect member of the new owning role, and you must have the
71   <literal>CREATEDB</literal> privilege.
72   (Note that superusers have all these privileges automatically.)
73  </para>
74
75  <para>
76   The fourth form changes the default tablespace of the database.
77   Only the database owner or a superuser can do this; you must also have
78   create privilege for the new tablespace.
79   This command physically moves any tables or indexes in the database's old
80   default tablespace to the new tablespace.  The new default tablespace
81   must be empty for this database, and no one can be connected to
82   the database.  Tables and indexes in non-default tablespaces are
83   unaffected.
84  </para>
85
86  <para>
87   The remaining forms change the session default for a run-time
88   configuration variable for a <productname>PostgreSQL</productname>
89   database. Whenever a new session is subsequently started in that
90   database, the specified value becomes the session default value.
91   The database-specific default overrides whatever setting is present
92   in <filename>postgresql.conf</> or has been received from the
93   <command>postgres</command> command line.  Only the database
94   owner or a superuser can change the session defaults for a
95   database.  Certain variables cannot be set this way, or can only be
96   set by a superuser.
97  </para>
98 </refsect1>
99
100 <refsect1>
101  <title>Parameters</title>
102
103    <variablelist>
104     <varlistentry>
105      <term><replaceable class="PARAMETER">name</replaceable></term>
106      <listitem>
107       <para>
108        The name of the database whose attributes are to be altered.
109       </para>
110      </listitem>
111     </varlistentry>
112
113      <varlistentry>
114       <term><replaceable class="parameter">allowconn</replaceable></term>
115       <listitem>
116        <para>
117         If false then no one can connect to this database.
118        </para>
119       </listitem>
120      </varlistentry>
121
122      <varlistentry>
123      <term><replaceable class="parameter">connlimit</replaceable></term>
124      <listitem>
125       <para>
126        How many concurrent connections can be made
127        to this database.  -1 means no limit.
128       </para>
129      </listitem>
130     </varlistentry>
131
132     <varlistentry>
133       <term><replaceable class="parameter">istemplate</replaceable></term>
134       <listitem>
135        <para>
136         If true, then this database can be cloned by any user with <literal>CREATEDB</literal>
137         privileges; if false, then only superusers or the owner of the
138         database can clone it.
139        </para>
140       </listitem>
141      </varlistentry>
142
143   <varlistentry>
144    <term><replaceable>new_name</replaceable></term>
145    <listitem>
146     <para>
147      The new name of the database.
148     </para>
149    </listitem>
150   </varlistentry>
151
152   <varlistentry>
153    <term><replaceable class="parameter">new_owner</replaceable></term>
154    <listitem>
155     <para>
156      The new owner of the database.
157     </para>
158    </listitem>
159   </varlistentry>
160
161   <varlistentry>
162    <term><replaceable class="parameter">new_tablespace</replaceable></term>
163    <listitem>
164     <para>
165      The new default tablespace of the database.
166     </para>
167
168     <para>
169      This form of the command cannot be executed inside a transaction block.
170     </para>
171    </listitem>
172   </varlistentry>
173
174     <varlistentry>
175      <term><replaceable>configuration_parameter</replaceable></term>
176      <term><replaceable>value</replaceable></term>
177      <listitem>
178       <para>
179        Set this database's session default for the specified configuration
180        parameter to the given value.  If
181        <replaceable>value</replaceable> is <literal>DEFAULT</literal>
182        or, equivalently, <literal>RESET</literal> is used, the
183        database-specific setting is removed, so the system-wide default
184        setting will be inherited in new sessions.  Use <literal>RESET
185        ALL</literal> to clear all database-specific settings.
186        <literal>SET FROM CURRENT</> saves the session's current value of
187        the parameter as the database-specific value.
188       </para>
189
190       <para>
191        See <xref linkend="sql-set"> and <xref linkend="runtime-config">
192        for more information about allowed parameter names
193        and values.
194       </para>
195      </listitem>
196     </varlistentry>
197  </variablelist>
198 </refsect1>
199
200 <refsect1>
201  <title>Notes</title>
202
203  <para>
204   It is also possible to tie a session default to a specific role
205   rather than to a database; see
206   <xref linkend="sql-alterrole">.
207   Role-specific settings override database-specific
208   ones if there is a conflict.
209  </para>
210 </refsect1>
211
212 <refsect1>
213  <title>Examples</title>
214
215  <para>
216   To disable index scans by default in the database
217   <literal>test</literal>:
218
219<programlisting>
220ALTER DATABASE test SET enable_indexscan TO off;
221</programlisting></para>
222 </refsect1>
223
224 <refsect1>
225  <title>Compatibility</title>
226
227  <para>
228   The <command>ALTER DATABASE</command> statement is a
229   <productname>PostgreSQL</productname> extension.
230  </para>
231 </refsect1>
232
233 <refsect1>
234  <title>See Also</title>
235
236  <simplelist type="inline">
237   <member><xref linkend="sql-createdatabase"></member>
238   <member><xref linkend="sql-dropdatabase"></member>
239   <member><xref linkend="sql-set"></member>
240   <member><xref linkend="sql-createtablespace"></member>
241  </simplelist>
242 </refsect1>
243</refentry>
244