2 $PostgreSQL: pgsql/doc/src/sgml/ref/alter_database.sgml,v 1.20 2007/09/03 18:46:29 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERDATABASE">
8 <refentrytitle id="sql-alterdatabase-title">ALTER DATABASE</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>ALTER DATABASE</refname>
14 <refpurpose>change a database</refpurpose>
17 <indexterm zone="sql-alterdatabase">
18 <primary>ALTER DATABASE</primary>
23 ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ] ]
25 where <replaceable class="PARAMETER">option</replaceable> can be:
27 CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
29 ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>newname</replaceable>
31 ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> OWNER TO <replaceable>new_owner</replaceable>
33 ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
34 ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
35 ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>configuration_parameter</replaceable>
36 ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET ALL
41 <title>Description</title>
44 <command>ALTER DATABASE</command> changes the attributes
49 The first form changes certain per-database settings. (See below for
50 details.) Only the database owner or a superuser can change these settings.
54 The second form changes the name of the database. Only the database
55 owner or a superuser can rename a database; non-superuser owners must
57 <literal>CREATEDB</literal> privilege. The current database cannot
58 be renamed. (Connect to a different database if you need to do
63 The third form changes the owner of the database.
64 To alter the owner, you must own the database and also be a direct or
65 indirect member of the new owning role, and you must have the
66 <literal>CREATEDB</literal> privilege.
67 (Note that superusers have all these privileges automatically.)
71 The remaining forms change the session default for a run-time
72 configuration variable for a <productname>PostgreSQL</productname>
73 database. Whenever a new session is subsequently started in that
74 database, the specified value becomes the session default value.
75 The database-specific default overrides whatever setting is present
76 in <filename>postgresql.conf</> or has been received from the
77 <command>postgres</command> command line. Only the database
78 owner or a superuser can change the session defaults for a
79 database. Certain variables cannot be set this way, or can only be
85 <title>Parameters</title>
89 <term><replaceable class="PARAMETER">name</replaceable></term>
92 The name of the database whose attributes are to be altered.
98 <term><replaceable class="parameter">connlimit</replaceable></term>
101 How many concurrent connections can be made
102 to this database. -1 means no limit.
108 <term><replaceable>newname</replaceable></term>
111 The new name of the database.
117 <term><replaceable class="parameter">new_owner</replaceable></term>
120 The new owner of the database.
126 <term><replaceable>configuration_parameter</replaceable></term>
127 <term><replaceable>value</replaceable></term>
130 Set this database's session default for the specified configuration
131 parameter to the given value. If
132 <replaceable>value</replaceable> is <literal>DEFAULT</literal>
133 or, equivalently, <literal>RESET</literal> is used, the
134 database-specific setting is removed, so the system-wide default
135 setting will be inherited in new sessions. Use <literal>RESET
136 ALL</literal> to clear all database-specific settings.
137 <literal>SET FROM CURRENT</> saves the session's current value of
138 the parameter as the database-specific value.
142 See <xref linkend="sql-set" endterm="sql-set-title"> and <xref linkend="runtime-config">
143 for more information about allowed parameter names
155 It is also possible to tie a session default to a specific role
156 rather than to a database; see
157 <xref linkend="sql-alterrole" endterm="sql-alterrole-title">.
158 Role-specific settings override database-specific
159 ones if there is a conflict.
164 <title>Examples</title>
167 To disable index scans by default in the database
168 <literal>test</literal>:
171 ALTER DATABASE test SET enable_indexscan TO off;
177 <title>Compatibility</title>
180 The <command>ALTER DATABASE</command> statement is a
181 <productname>PostgreSQL</productname> extension.
186 <title>See Also</title>
188 <simplelist type="inline">
189 <member><xref linkend="sql-createdatabase" endterm="sql-createdatabase-title"></member>
190 <member><xref linkend="sql-dropdatabase" endterm="sql-dropdatabase-title"></member>
191 <member><xref linkend="sql-set" endterm="sql-set-title"></member>