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 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 16.3 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 generator4  </p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">5ALTER 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    [ START [ WITH ] <em class="replaceable"><code>start</code></em> ]10    [ RESTART [ [ WITH ] <em class="replaceable"><code>restart</code></em> ] ]11    [ CACHE <em class="replaceable"><code>cache</code></em> ] [ [ NO ] CYCLE ]12    [ OWNED BY { <em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em> | NONE } ]13ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> SET { LOGGED | UNLOGGED }14ALTER 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 }15ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> RENAME TO <em class="replaceable"><code>new_name</code></em>16ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> SET SCHEMA <em class="replaceable"><code>new_schema</code></em>17</pre></div><div class="refsect1" id="id-1.9.3.30.5"><h2>Description</h2><p>18   <code class="command">ALTER SEQUENCE</code> changes the parameters of an existing19   sequence generator.  Any parameters not specifically set in the20   <code class="command">ALTER SEQUENCE</code> command retain their prior settings.21  </p><p>22   You must own the sequence to use <code class="command">ALTER SEQUENCE</code>.23   To change a sequence's schema, you must also have <code class="literal">CREATE</code>24   privilege on the new schema.25   To alter the owner, you must be able to <code class="literal">SET ROLE</code> to the26   new owning role, and that role must have <code class="literal">CREATE</code>27   privilege on the sequence's schema.28   (These restrictions enforce that altering the owner29   doesn't do anything you couldn't do by dropping and recreating the sequence.30   However, a superuser can alter ownership of any sequence anyway.)31  </p></div><div class="refsect1" id="id-1.9.3.30.6"><h2>Parameters</h2><p>32    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>33        The name (optionally schema-qualified) of a sequence to be altered.34       </p></dd><dt><span class="term"><code class="literal">IF EXISTS</code></span></dt><dd><p>35        Do not throw an error if the sequence does not exist. A notice is issued36        in this case.37       </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>38        The optional39        clause <code class="literal">AS <em class="replaceable"><code>data_type</code></em></code>40        changes the data type of the sequence.  Valid types are41        <code class="literal">smallint</code>, <code class="literal">integer</code>,42        and <code class="literal">bigint</code>.43       </p><p>44        Changing the data type automatically changes the minimum and maximum45        values of the sequence if and only if the previous minimum and maximum46        values were the minimum or maximum value of the old data type (in47        other words, if the sequence had been created using <code class="literal">NO48        MINVALUE</code> or <code class="literal">NO MAXVALUE</code>, implicitly or49        explicitly).  Otherwise, the minimum and maximum values are preserved,50        unless new values are given as part of the same command.  If the51        minimum and maximum values do not fit into the new data type, an error52        will be generated.53       </p></dd><dt><span class="term"><em class="replaceable"><code>increment</code></em></span></dt><dd><p>54        The clause <code class="literal">INCREMENT BY <em class="replaceable"><code>increment</code></em></code> is55        optional. A positive value will make an ascending sequence, a56        negative one a descending sequence.  If unspecified, the old57        increment value will be maintained.58       </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>59        The optional clause <code class="literal">MINVALUE <em class="replaceable"><code>minvalue</code></em></code> determines60        the minimum value a sequence can generate. If <code class="literal">NO61        MINVALUE</code> is specified, the defaults of 1 and62        the minimum value of the data type for ascending and descending sequences,63        respectively, will be used.  If neither option is specified,64        the current minimum value will be maintained.65       </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>66        The optional clause <code class="literal">MAXVALUE <em class="replaceable"><code>maxvalue</code></em></code> determines67        the maximum value for the sequence. If <code class="literal">NO68        MAXVALUE</code> is specified, the defaults of69        the maximum value of the data type and -1 for ascending and descending70        sequences, respectively, will be used.  If neither option is71        specified, the current maximum value will be maintained.72       </p></dd><dt><span class="term"><em class="replaceable"><code>start</code></em></span></dt><dd><p>73        The optional clause <code class="literal">START WITH <em class="replaceable"><code>start</code></em></code> changes the74        recorded start value of the sequence.  This has no effect on the75        <span class="emphasis"><em>current</em></span> sequence value; it simply sets the value76        that future <code class="command">ALTER SEQUENCE RESTART</code> commands will use.77       </p></dd><dt><span class="term"><em class="replaceable"><code>restart</code></em></span></dt><dd><p>78        The optional clause <code class="literal">RESTART [ WITH <em class="replaceable"><code>restart</code></em> ]</code> changes the79        current value of the sequence.  This is similar to calling the80        <code class="function">setval</code> function with <code class="literal">is_called</code> =81        <code class="literal">false</code>: the specified value will be returned by the82        <span class="emphasis"><em>next</em></span> call of <code class="function">nextval</code>.83        Writing <code class="literal">RESTART</code> with no <em class="replaceable"><code>restart</code></em> value is equivalent to supplying84        the start value that was recorded by <code class="command">CREATE SEQUENCE</code>85        or last set by <code class="command">ALTER SEQUENCE START WITH</code>.86       </p><p>87        In contrast to a <code class="function">setval</code> call,88        a <code class="literal">RESTART</code> operation on a sequence is transactional89        and blocks concurrent transactions from obtaining numbers from the90        same sequence. If that's not the desired mode of91        operation, <code class="function">setval</code> should be used.92       </p></dd><dt><span class="term"><em class="replaceable"><code>cache</code></em></span></dt><dd><p>93        The clause <code class="literal">CACHE <em class="replaceable"><code>cache</code></em></code> enables94        sequence numbers to be preallocated and stored in memory for95        faster access. The minimum value is 1 (only one value can be96        generated at a time, i.e., no cache).  If unspecified, the old97        cache value will be maintained.98       </p></dd><dt><span class="term"><code class="literal">CYCLE</code></span></dt><dd><p>99        The optional <code class="literal">CYCLE</code> key word can be used to enable100        the sequence to wrap around when the101        <em class="replaceable"><code>maxvalue</code></em> or102        <em class="replaceable"><code>minvalue</code></em> has been103        reached by104        an ascending or descending sequence respectively. If the limit is105        reached, the next number generated will be the106        <em class="replaceable"><code>minvalue</code></em> or107        <em class="replaceable"><code>maxvalue</code></em>,108        respectively.109       </p></dd><dt><span class="term"><code class="literal">NO CYCLE</code></span></dt><dd><p>110        If the optional <code class="literal">NO CYCLE</code> key word is111        specified, any calls to <code class="function">nextval</code> after the112        sequence has reached its maximum value will return an error.113        If neither <code class="literal">CYCLE</code> or <code class="literal">NO114        CYCLE</code> are specified, the old cycle behavior will be115        maintained.116       </p></dd><dt><span class="term"><code class="literal">SET { LOGGED | UNLOGGED }</code></span></dt><dd><p>117        This form changes the sequence from unlogged to logged or vice-versa118        (see <a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>).  It cannot be applied to a119        temporary sequence.120       </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>121      The <code class="literal">OWNED BY</code> option causes the sequence to be122      associated with a specific table column, such that if that column123      (or its whole table) is dropped, the sequence will be automatically124      dropped as well.  If specified, this association replaces any125      previously specified association for the sequence.  The specified126      table must have the same owner and be in the same schema as the127      sequence.128      Specifying <code class="literal">OWNED BY NONE</code> removes any existing129      association, making the sequence <span class="quote">“<span class="quote">free-standing</span>”</span>.130     </p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>131      The user name of the new owner of the sequence.132     </p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>133      The new name for the sequence.134     </p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p>135      The new schema for the sequence.136     </p></dd></dl></div><p>137   </p></div><div class="refsect1" id="id-1.9.3.30.7"><h2>Notes</h2><p>138   <code class="command">ALTER SEQUENCE</code> will not immediately affect139   <code class="function">nextval</code> results in backends,140   other than the current one, that have preallocated (cached) sequence141   values. They will use up all cached values prior to noticing the changed142   sequence generation parameters.  The current backend will be affected143   immediately.144  </p><p>145   <code class="command">ALTER SEQUENCE</code> does not affect the <code class="function">currval</code>146   status for the sequence.  (Before <span class="productname">PostgreSQL</span>147   8.3, it sometimes did.)148  </p><p>149   <code class="command">ALTER SEQUENCE</code> blocks150   concurrent <code class="function">nextval</code>, <code class="function">currval</code>,151   <code class="function">lastval</code>, and <code class="command">setval</code> calls.152  </p><p>153   For historical reasons, <code class="command">ALTER TABLE</code> can be used with154   sequences too; but the only variants of <code class="command">ALTER TABLE</code>155   that are allowed with sequences are equivalent to the forms shown above.156  </p></div><div class="refsect1" id="id-1.9.3.30.8"><h2>Examples</h2><p>157   Restart a sequence called <code class="literal">serial</code>, at 105:158</p><pre class="programlisting">159ALTER SEQUENCE serial RESTART WITH 105;160</pre></div><div class="refsect1" id="id-1.9.3.30.9"><h2>Compatibility</h2><p>161   <code class="command">ALTER SEQUENCE</code> conforms to the <acronym class="acronym">SQL</acronym>162   standard, except for the <code class="literal">AS</code>, <code class="literal">START WITH</code>,163   <code class="literal">OWNED BY</code>, <code class="literal">OWNER TO</code>, <code class="literal">RENAME TO</code>, and164   <code class="literal">SET SCHEMA</code> clauses, which are165   <span class="productname">PostgreSQL</span> extensions.166  </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER SERVER</td></tr></table></div></body></html>