begriffs open source - ai-pg/blob - full-docs/src/sgml/html/sql-altersequence.html

   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 SEQUENCE</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-alterschema.html" title="ALTER SCHEMA" /><link rel="next" href="sql-alterserver.html" title="ALTER SERVER" /></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 SEQUENCE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterschema.html" title="ALTER SCHEMA">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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-alterserver.html" title="ALTER SERVER">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-ALTERSEQUENCE"><div class="titlepage"></div><a id="id-1.9.3.30.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER SEQUENCE</span></h2><p>ALTER SEQUENCE —
   3    change the definition of a sequence generator
   4   </p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
   5 ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
   6     [ AS <em class="replaceable"><code>data_type</code></em> ]
   7     [ INCREMENT [ BY ] <em class="replaceable"><code>increment</code></em> ]
   8     [ MINVALUE <em class="replaceable"><code>minvalue</code></em> | NO MINVALUE ] [ MAXVALUE <em class="replaceable"><code>maxvalue</code></em> | NO MAXVALUE ]
   9     [ [ NO ] CYCLE ]
  10     [ START [ WITH ] <em class="replaceable"><code>start</code></em> ]
  11     [ RESTART [ [ WITH ] <em class="replaceable"><code>restart</code></em> ] ]
  12     [ CACHE <em class="replaceable"><code>cache</code></em> ]
  13     [ OWNED BY { <em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em> | NONE } ]
  14 ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> SET { LOGGED | UNLOGGED }
  15 ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
  16 ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> RENAME TO <em class="replaceable"><code>new_name</code></em>
  17 ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
  18 </pre></div><div class="refsect1" id="id-1.9.3.30.5"><h2>Description</h2><p>
  19    <code class="command">ALTER SEQUENCE</code> changes the parameters of an existing
  20    sequence generator.  Any parameters not specifically set in the
  21    <code class="command">ALTER SEQUENCE</code> command retain their prior settings.
  22   </p><p>
  23    You must own the sequence to use <code class="command">ALTER SEQUENCE</code>.
  24    To change a sequence's schema, you must also have <code class="literal">CREATE</code>
  25    privilege on the new schema.
  26    To alter the owner, you must be able to <code class="literal">SET ROLE</code> to the
  27    new owning role, and that role must have <code class="literal">CREATE</code>
  28    privilege on the sequence's schema.
  29    (These restrictions enforce that altering the owner
  30    doesn't do anything you couldn't do by dropping and recreating the sequence.
  31    However, a superuser can alter ownership of any sequence anyway.)
  32   </p></div><div class="refsect1" id="id-1.9.3.30.6"><h2>Parameters</h2><p>
  33     </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
  34         The name (optionally schema-qualified) of a sequence to be altered.
  35        </p></dd><dt><span class="term"><code class="literal">IF EXISTS</code></span></dt><dd><p>
  36         Do not throw an error if the sequence does not exist. A notice is issued
  37         in this case.
  38        </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>
  39         The optional
  40         clause <code class="literal">AS <em class="replaceable"><code>data_type</code></em></code>
  41         changes the data type of the sequence.  Valid types are
  42         <code class="literal">smallint</code>, <code class="literal">integer</code>,
  43         and <code class="literal">bigint</code>.
  44        </p><p>
  45         Changing the data type automatically changes the minimum and maximum
  46         values of the sequence if and only if the previous minimum and maximum
  47         values were the minimum or maximum value of the old data type (in
  48         other words, if the sequence had been created using <code class="literal">NO
  49         MINVALUE</code> or <code class="literal">NO MAXVALUE</code>, implicitly or
  50         explicitly).  Otherwise, the minimum and maximum values are preserved,
  51         unless new values are given as part of the same command.  If the
  52         minimum and maximum values do not fit into the new data type, an error
  53         will be generated.
  54        </p></dd><dt><span class="term"><em class="replaceable"><code>increment</code></em></span></dt><dd><p>
  55         The clause <code class="literal">INCREMENT BY <em class="replaceable"><code>increment</code></em></code> is
  56         optional. A positive value will make an ascending sequence, a
  57         negative one a descending sequence.  If unspecified, the old
  58         increment value will be maintained.
  59        </p></dd><dt><span class="term"><em class="replaceable"><code>minvalue</code></em><br /></span><span class="term"><code class="literal">NO MINVALUE</code></span></dt><dd><p>
  60         The optional clause <code class="literal">MINVALUE <em class="replaceable"><code>minvalue</code></em></code> determines
  61         the minimum value a sequence can generate. If <code class="literal">NO
  62         MINVALUE</code> is specified, the defaults of 1 and
  63         the minimum value of the data type for ascending and descending sequences,
  64         respectively, will be used.  If neither option is specified,
  65         the current minimum value will be maintained.
  66        </p></dd><dt><span class="term"><em class="replaceable"><code>maxvalue</code></em><br /></span><span class="term"><code class="literal">NO MAXVALUE</code></span></dt><dd><p>
  67         The optional clause <code class="literal">MAXVALUE <em class="replaceable"><code>maxvalue</code></em></code> determines
  68         the maximum value for the sequence. If <code class="literal">NO
  69         MAXVALUE</code> is specified, the defaults of
  70         the maximum value of the data type and -1 for ascending and descending
  71         sequences, respectively, will be used.  If neither option is
  72         specified, the current maximum value will be maintained.
  73        </p></dd><dt><span class="term"><code class="literal">CYCLE</code></span></dt><dd><p>
  74         The optional <code class="literal">CYCLE</code> key word can be used to enable
  75         the sequence to wrap around when the
  76         <em class="replaceable"><code>maxvalue</code></em> or
  77         <em class="replaceable"><code>minvalue</code></em> has been
  78         reached by
  79         an ascending or descending sequence respectively. If the limit is
  80         reached, the next number generated will be the
  81         <em class="replaceable"><code>minvalue</code></em> or
  82         <em class="replaceable"><code>maxvalue</code></em>,
  83         respectively.
  84        </p></dd><dt><span class="term"><code class="literal">NO CYCLE</code></span></dt><dd><p>
  85         If the optional <code class="literal">NO CYCLE</code> key word is
  86         specified, any calls to <code class="function">nextval</code> after the
  87         sequence has reached its maximum value will return an error.
  88         If neither <code class="literal">CYCLE</code> or <code class="literal">NO
  89         CYCLE</code> are specified, the old cycle behavior will be
  90         maintained.
  91        </p></dd><dt><span class="term"><em class="replaceable"><code>start</code></em></span></dt><dd><p>
  92         The optional clause <code class="literal">START WITH <em class="replaceable"><code>start</code></em></code> changes the
  93         recorded start value of the sequence.  This has no effect on the
  94         <span class="emphasis"><em>current</em></span> sequence value; it simply sets the value
  95         that future <code class="command">ALTER SEQUENCE RESTART</code> commands will use.
  96        </p></dd><dt><span class="term"><em class="replaceable"><code>restart</code></em></span></dt><dd><p>
  97         The optional clause <code class="literal">RESTART [ WITH <em class="replaceable"><code>restart</code></em> ]</code> changes the
  98         current value of the sequence.  This is similar to calling the
  99         <code class="function">setval</code> function with <code class="literal">is_called</code> =
 100         <code class="literal">false</code>: the specified value will be returned by the
 101         <span class="emphasis"><em>next</em></span> call of <code class="function">nextval</code>.
 102         Writing <code class="literal">RESTART</code> with no <em class="replaceable"><code>restart</code></em> value is equivalent to supplying
 103         the start value that was recorded by <code class="command">CREATE SEQUENCE</code>
 104         or last set by <code class="command">ALTER SEQUENCE START WITH</code>.
 105        </p><p>
 106         In contrast to a <code class="function">setval</code> call,
 107         a <code class="literal">RESTART</code> operation on a sequence is transactional
 108         and blocks concurrent transactions from obtaining numbers from the
 109         same sequence. If that's not the desired mode of
 110         operation, <code class="function">setval</code> should be used.
 111        </p></dd><dt><span class="term"><em class="replaceable"><code>cache</code></em></span></dt><dd><p>
 112         The clause <code class="literal">CACHE <em class="replaceable"><code>cache</code></em></code> enables
 113         sequence numbers to be preallocated and stored in memory for
 114         faster access. The minimum value is 1 (only one value can be
 115         generated at a time, i.e., no cache).  If unspecified, the old
 116         cache value will be maintained.
 117        </p></dd><dt><span class="term"><code class="literal">SET { LOGGED | UNLOGGED }</code></span></dt><dd><p>
 118         This form changes the sequence from unlogged to logged or vice-versa
 119         (see <a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>).  It cannot be applied to a
 120         temporary sequence.
 121        </p></dd><dt><span class="term"><code class="literal">OWNED BY</code> <em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em><br /></span><span class="term"><code class="literal">OWNED BY NONE</code></span></dt><dd><p>
 122       The <code class="literal">OWNED BY</code> option causes the sequence to be
 123       associated with a specific table column, such that if that column
 124       (or its whole table) is dropped, the sequence will be automatically
 125       dropped as well.  If specified, this association replaces any
 126       previously specified association for the sequence.  The specified
 127       table must have the same owner and be in the same schema as the
 128       sequence.
 129       Specifying <code class="literal">OWNED BY NONE</code> removes any existing
 130       association, making the sequence <span class="quote">“<span class="quote">free-standing</span>”</span>.
 131      </p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>
 132       The user name of the new owner of the sequence.
 133      </p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>
 134       The new name for the sequence.
 135      </p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p>
 136       The new schema for the sequence.
 137      </p></dd></dl></div><p>
 138    </p></div><div class="refsect1" id="id-1.9.3.30.7"><h2>Notes</h2><p>
 139    <code class="command">ALTER SEQUENCE</code> will not immediately affect
 140    <code class="function">nextval</code> results in backends,
 141    other than the current one, that have preallocated (cached) sequence
 142    values. They will use up all cached values prior to noticing the changed
 143    sequence generation parameters.  The current backend will be affected
 144    immediately.
 145   </p><p>
 146    <code class="command">ALTER SEQUENCE</code> does not affect the <code class="function">currval</code>
 147    status for the sequence.  (Before <span class="productname">PostgreSQL</span>
 148    8.3, it sometimes did.)
 149   </p><p>
 150    <code class="command">ALTER SEQUENCE</code> blocks
 151    concurrent <code class="function">nextval</code>, <code class="function">currval</code>,
 152    <code class="function">lastval</code>, and <code class="command">setval</code> calls.
 153   </p><p>
 154    For historical reasons, <code class="command">ALTER TABLE</code> can be used with
 155    sequences too; but the only variants of <code class="command">ALTER TABLE</code>
 156    that are allowed with sequences are equivalent to the forms shown above.
 157   </p></div><div class="refsect1" id="id-1.9.3.30.8"><h2>Examples</h2><p>
 158    Restart a sequence called <code class="literal">serial</code>, at 105:
 159 </p><pre class="programlisting">
 160 ALTER SEQUENCE serial RESTART WITH 105;
 161 </pre></div><div class="refsect1" id="id-1.9.3.30.9"><h2>Compatibility</h2><p>
 162    <code class="command">ALTER SEQUENCE</code> conforms to the <acronym class="acronym">SQL</acronym>
 163    standard, except for the <code class="literal">AS</code>, <code class="literal">START WITH</code>,
 164    <code class="literal">OWNED BY</code>, <code class="literal">OWNER TO</code>, <code class="literal">RENAME TO</code>, and
 165    <code class="literal">SET SCHEMA</code> clauses, which are
 166    <span class="productname">PostgreSQL</span> extensions.
 167   </p></div><div class="refsect1" id="id-1.9.3.30.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>, <a class="xref" href="sql-dropsequence.html" title="DROP SEQUENCE"><span class="refentrytitle">DROP SEQUENCE</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-alterschema.html" title="ALTER SCHEMA">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-alterserver.html" title="ALTER SERVER">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER SCHEMA </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER SERVER</td></tr></table></div></body></html>