codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>ALTER DATABASE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-alterconversion.html" title="ALTER CONVERSION" /><link rel="next" href="sql-alterdefaultprivileges.html" title="ALTER DEFAULT PRIVILEGES" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ALTER DATABASE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterconversion.html" title="ALTER CONVERSION">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-alterdefaultprivileges.html" title="ALTER DEFAULT PRIVILEGES">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-ALTERDATABASE"><div class="titlepage"></div><a id="id-1.9.3.7.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER DATABASE</span></h2><p>ALTER DATABASE — change a database</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3ALTER DATABASE <em class="replaceable"><code>name</code></em> [ [ WITH ] <em class="replaceable"><code>option</code></em> [ ... ] ]4 5<span class="phrase">where <em class="replaceable"><code>option</code></em> can be:</span>6 7    ALLOW_CONNECTIONS <em class="replaceable"><code>allowconn</code></em>8    CONNECTION LIMIT <em class="replaceable"><code>connlimit</code></em>9    IS_TEMPLATE <em class="replaceable"><code>istemplate</code></em>10 11ALTER DATABASE <em class="replaceable"><code>name</code></em> RENAME TO <em class="replaceable"><code>new_name</code></em>12 13ALTER DATABASE <em class="replaceable"><code>name</code></em> OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }14 15ALTER DATABASE <em class="replaceable"><code>name</code></em> SET TABLESPACE <em class="replaceable"><code>new_tablespace</code></em>16 17ALTER DATABASE <em class="replaceable"><code>name</code></em> REFRESH COLLATION VERSION18 19ALTER DATABASE <em class="replaceable"><code>name</code></em> SET <em class="replaceable"><code>configuration_parameter</code></em> { TO | = } { <em class="replaceable"><code>value</code></em> | DEFAULT }20ALTER DATABASE <em class="replaceable"><code>name</code></em> SET <em class="replaceable"><code>configuration_parameter</code></em> FROM CURRENT21ALTER DATABASE <em class="replaceable"><code>name</code></em> RESET <em class="replaceable"><code>configuration_parameter</code></em>22ALTER DATABASE <em class="replaceable"><code>name</code></em> RESET ALL23</pre></div><div class="refsect1" id="id-1.9.3.7.5"><h2>Description</h2><p>24   <code class="command">ALTER DATABASE</code> changes the attributes25   of a database.26  </p><p>27   The first form changes certain per-database settings.  (See below for28   details.)  Only the database owner or a superuser can change these settings.29  </p><p>30   The second form changes the name of the database.  Only the database31   owner or a superuser can rename a database; non-superuser owners must32   also have the33   <code class="literal">CREATEDB</code> privilege.  The current database cannot34   be renamed.  (Connect to a different database if you need to do35   that.)36  </p><p>37   The third form changes the owner of the database.38   To alter the owner, you must be able to <code class="literal">SET ROLE</code> to the39   new owning role, and you must have the40   <code class="literal">CREATEDB</code> privilege.41   (Note that superusers have all these privileges automatically.)42  </p><p>43   The fourth form changes the default tablespace of the database.44   Only the database owner or a superuser can do this; you must also have45   create privilege for the new tablespace.46   This command physically moves any tables or indexes in the database's old47   default tablespace to the new tablespace.  The new default tablespace48   must be empty for this database, and no one can be connected to49   the database.  Tables and indexes in non-default tablespaces are50   unaffected.51  </p><p>52   The remaining forms change the session default for a run-time53   configuration variable for a <span class="productname">PostgreSQL</span>54   database. Whenever a new session is subsequently started in that55   database, the specified value becomes the session default value.56   The database-specific default overrides whatever setting is present57   in <code class="filename">postgresql.conf</code> or has been received from the58   <code class="command">postgres</code> command line.  Only the database59   owner or a superuser can change the session defaults for a60   database.  Certain variables cannot be set this way, or can only be61   set by a superuser.62  </p></div><div class="refsect1" id="id-1.9.3.7.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>63        The name of the database whose attributes are to be altered.64       </p></dd><dt><span class="term"><em class="replaceable"><code>allowconn</code></em></span></dt><dd><p>65         If false then no one can connect to this database.66        </p></dd><dt><span class="term"><em class="replaceable"><code>connlimit</code></em></span></dt><dd><p>67        How many concurrent connections can be made68        to this database.  -1 means no limit.69       </p></dd><dt><span class="term"><em class="replaceable"><code>istemplate</code></em></span></dt><dd><p>70         If true, then this database can be cloned by any user with <code class="literal">CREATEDB</code>71         privileges; if false, then only superusers or the owner of the72         database can clone it.73        </p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>74      The new name of the database.75     </p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>76      The new owner of the database.77     </p></dd><dt><span class="term"><em class="replaceable"><code>new_tablespace</code></em></span></dt><dd><p>78      The new default tablespace of the database.79     </p><p>80      This form of the command cannot be executed inside a transaction block.81     </p></dd><dt><span class="term"><code class="literal">REFRESH COLLATION VERSION</code></span></dt><dd><p>82      Update the database collation version.  See <a class="xref" href="sql-altercollation.html#SQL-ALTERCOLLATION-NOTES" title="Notes">Notes</a> for background.83     </p></dd><dt><span class="term"><em class="replaceable"><code>configuration_parameter</code></em><br /></span><span class="term"><em class="replaceable"><code>value</code></em></span></dt><dd><p>84        Set this database's session default for the specified configuration85        parameter to the given value.  If86        <em class="replaceable"><code>value</code></em> is <code class="literal">DEFAULT</code>87        or, equivalently, <code class="literal">RESET</code> is used, the88        database-specific setting is removed, so the system-wide default89        setting will be inherited in new sessions.  Use <code class="literal">RESET90        ALL</code> to clear all database-specific settings.91        <code class="literal">SET FROM CURRENT</code> saves the session's current value of92        the parameter as the database-specific value.93       </p><p>94        See <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a> and <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>95        for more information about allowed parameter names96        and values.97       </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.7.7"><h2>Notes</h2><p>98   It is also possible to tie a session default to a specific role99   rather than to a database; see100   <a class="xref" href="sql-alterrole.html" title="ALTER ROLE"><span class="refentrytitle">ALTER ROLE</span></a>.101   Role-specific settings override database-specific102   ones if there is a conflict.103  </p></div><div class="refsect1" id="id-1.9.3.7.8"><h2>Examples</h2><p>104   To disable index scans by default in the database105   <code class="literal">test</code>:106 107</p><pre class="programlisting">108ALTER DATABASE test SET enable_indexscan TO off;109</pre></div><div class="refsect1" id="id-1.9.3.7.9"><h2>Compatibility</h2><p>110   The <code class="command">ALTER DATABASE</code> statement is a111   <span class="productname">PostgreSQL</span> extension.112  </p></div><div class="refsect1" id="id-1.9.3.7.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createdatabase.html" title="CREATE DATABASE"><span class="refentrytitle">CREATE DATABASE</span></a>, <a class="xref" href="sql-dropdatabase.html" title="DROP DATABASE"><span class="refentrytitle">DROP DATABASE</span></a>, <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a>, <a class="xref" href="sql-createtablespace.html" title="CREATE TABLESPACE"><span class="refentrytitle">CREATE TABLESPACE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-alterconversion.html" title="ALTER CONVERSION">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-alterdefaultprivileges.html" title="ALTER DEFAULT PRIVILEGES">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER CONVERSION </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER DEFAULT PRIVILEGES</td></tr></table></div></body></html>