doc/src/sgml/ref/alter_sequence.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-ALTERSEQUENCE">
   7  <refmeta>
   8   <refentrytitle id="SQL-ALTERSEQUENCE-TITLE">ALTER SEQUENCE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>ALTER SEQUENCE</refname>
  15   <refpurpose>
  16    change the definition of a sequence generator
  17   </refpurpose>
  18  </refnamediv>
  19
  20  <indexterm zone="sql-altersequence">
  21   <primary>ALTER SEQUENCE</primary>
  22  </indexterm>
  23
  24  <refsynopsisdiv>
  25   <synopsis>
  26 ALTER SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
  27     [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
  28     [ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
  29     [ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
  30     [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
  31     [ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
  32 ALTER SEQUENCE <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
  33 ALTER SEQUENCE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
  34 ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
  35   </synopsis>
  36  </refsynopsisdiv>
  37
  38  <refsect1>
  39   <title>Description</title>
  40
  41   <para>
  42    <command>ALTER SEQUENCE</command> changes the parameters of an existing
  43    sequence generator.  Any parameters not specifically set in the
  44    <command>ALTER SEQUENCE</command> command retain their prior settings.
  45   </para>
  46
  47   <para>
  48    You must own the sequence to use <command>ALTER SEQUENCE</>.
  49    To change a sequence's schema, you must also have <literal>CREATE</>
  50    privilege on the new schema.
  51    To alter the owner, you must also be a direct or indirect member of the new
  52    owning role, and that role must have <literal>CREATE</literal> privilege on
  53    the sequence's schema.  (These restrictions enforce that altering the owner
  54    doesn't do anything you couldn't do by dropping and recreating the sequence.
  55    However, a superuser can alter ownership of any sequence anyway.)
  56   </para>
  57  </refsect1>
  58
  59  <refsect1>
  60   <title>Parameters</title>
  61
  62    <para>
  63     <variablelist>
  64      <varlistentry>
  65       <term><replaceable class="parameter">name</replaceable></term>
  66       <listitem>
  67        <para>
  68         The name (optionally schema-qualified) of a sequence to be altered.
  69        </para>
  70       </listitem>
  71      </varlistentry>
  72
  73      <varlistentry>
  74       <term><replaceable class="parameter">increment</replaceable></term>
  75       <listitem>
  76        <para>
  77         The clause <literal>INCREMENT BY <replaceable
  78         class="parameter">increment</replaceable></literal> is
  79         optional. A positive value will make an ascending sequence, a
  80         negative one a descending sequence.  If unspecified, the old
  81         increment value will be maintained.
  82        </para>
  83       </listitem>
  84      </varlistentry>
  85
  86      <varlistentry>
  87       <term><replaceable class="parameter">minvalue</replaceable></term>
  88       <term><literal>NO MINVALUE</literal></term>
  89       <listitem>
  90        <para>
  91         The optional clause <literal>MINVALUE <replaceable
  92         class="parameter">minvalue</replaceable></literal> determines
  93         the minimum value a sequence can generate. If <literal>NO
  94         MINVALUE</literal> is specified, the defaults of 1 and
  95         -2<superscript>63</>-1 for ascending and descending sequences,
  96         respectively, will be used.  If neither option is specified,
  97         the current minimum value will be maintained.
  98        </para>
  99       </listitem>
 100      </varlistentry>
 101
 102      <varlistentry>
 103       <term><replaceable class="parameter">maxvalue</replaceable></term>
 104       <term><literal>NO MAXVALUE</literal></term>
 105       <listitem>
 106        <para>
 107         The optional clause <literal>MAXVALUE <replaceable
 108         class="parameter">maxvalue</replaceable></literal> determines
 109         the maximum value for the sequence. If <literal>NO
 110         MAXVALUE</literal> is specified, the defaults are
 111         2<superscript>63</>-1 and -1 for ascending and descending
 112         sequences, respectively, will be used.  If neither option is
 113         specified, the current maximum value will be maintained.
 114        </para>
 115       </listitem>
 116      </varlistentry>
 117
 118      <varlistentry>
 119       <term><replaceable class="parameter">start</replaceable></term>
 120       <listitem>
 121        <para>
 122         The optional clause <literal>START WITH <replaceable
 123         class="parameter">start</replaceable></literal> changes the
 124         recorded start value of the sequence.  This has no effect on the
 125         <emphasis>current</> sequence value; it simply sets the value
 126         that future <command>ALTER SEQUENCE RESTART</> commands will use.
 127        </para>
 128       </listitem>
 129      </varlistentry>
 130
 131      <varlistentry>
 132       <term><replaceable class="parameter">restart</replaceable></term>
 133       <listitem>
 134        <para>
 135         The optional clause <literal>RESTART [ WITH <replaceable
 136         class="parameter">restart</replaceable> ]</literal> changes the
 137         current value of the sequence.  This is equivalent to calling the
 138         <function>setval</> function with <literal>is_called</literal> =
 139         <literal>false</>: the specified value will be returned by the
 140         <emphasis>next</> call of <function>nextval</>.
 141         Writing <literal>RESTART</> with no <replaceable
 142         class="parameter">restart</> value is equivalent to supplying
 143         the start value that was recorded by <command>CREATE SEQUENCE</>
 144         or last set by <command>ALTER SEQUENCE START WITH</>.
 145        </para>
 146       </listitem>
 147      </varlistentry>
 148
 149      <varlistentry>
 150       <term><replaceable class="parameter">cache</replaceable></term>
 151       <listitem>
 152        <para>
 153         The clause <literal>CACHE <replaceable
 154         class="parameter">cache</replaceable></literal> enables
 155         sequence numbers to be preallocated and stored in memory for
 156         faster access. The minimum value is 1 (only one value can be
 157         generated at a time, i.e., no cache).  If unspecified, the old
 158         cache value will be maintained.
 159        </para>
 160       </listitem>
 161      </varlistentry>
 162
 163      <varlistentry>
 164       <term><literal>CYCLE</literal></term>
 165       <listitem>
 166        <para>
 167         The optional <literal>CYCLE</literal> key word can be used to enable
 168         the sequence to wrap around when the
 169         <replaceable class="parameter">maxvalue</replaceable> or
 170         <replaceable class="parameter">minvalue</replaceable> has been
 171         reached by
 172         an ascending or descending sequence respectively. If the limit is
 173         reached, the next number generated will be the
 174         <replaceable class="parameter">minvalue</replaceable> or
 175         <replaceable class="parameter">maxvalue</replaceable>,
 176         respectively.
 177        </para>
 178       </listitem>
 179      </varlistentry>
 180
 181      <varlistentry>
 182       <term><literal>NO CYCLE</literal></term>
 183       <listitem>
 184        <para>
 185         If the optional <literal>NO CYCLE</literal> key word is
 186         specified, any calls to <function>nextval</function> after the
 187         sequence has reached its maximum value will return an error.
 188         If neither <literal>CYCLE</literal> or <literal>NO
 189         CYCLE</literal> are specified, the old cycle behavior will be
 190         maintained.
 191        </para>
 192       </listitem>
 193      </varlistentry>
 194
 195    <varlistentry>
 196     <term><literal>OWNED BY</literal> <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable></term>
 197     <term><literal>OWNED BY NONE</literal></term>
 198     <listitem>
 199      <para>
 200       The <literal>OWNED BY</literal> option causes the sequence to be
 201       associated with a specific table column, such that if that column
 202       (or its whole table) is dropped, the sequence will be automatically
 203       dropped as well.  If specified, this association replaces any
 204       previously specified association for the sequence.  The specified
 205       table must have the same owner and be in the same schema as the
 206       sequence.
 207       Specifying <literal>OWNED BY NONE</literal> removes any existing
 208       association, making the sequence <quote>free-standing</>.
 209      </para>
 210     </listitem>
 211    </varlistentry>
 212
 213    <varlistentry>
 214     <term><replaceable class="PARAMETER">new_owner</replaceable></term>
 215     <listitem>
 216      <para>
 217       The user name of the new owner of the sequence.
 218      </para>
 219     </listitem>
 220    </varlistentry>
 221
 222    <varlistentry>
 223     <term><replaceable class="parameter">new_name</replaceable></term>
 224     <listitem>
 225      <para>
 226       The new name for the sequence.
 227      </para>
 228     </listitem>
 229    </varlistentry>
 230
 231    <varlistentry>
 232     <term><replaceable class="parameter">new_schema</replaceable></term>
 233     <listitem>
 234      <para>
 235       The new schema for the sequence.
 236      </para>
 237     </listitem>
 238    </varlistentry>
 239
 240     </variablelist>
 241    </para>
 242   </refsect1>
 243
 244  <refsect1>
 245   <title>Notes</title>
 246
 247   <para>
 248    To avoid blocking of concurrent transactions that obtain numbers from the
 249    same sequence, <command>ALTER SEQUENCE</command>'s effects on the sequence
 250    generation parameters are never rolled back; those changes take effect
 251    immediately and are not reversible.  However, the <literal>OWNED BY</>,
 252    <literal>OWNER TO</>, <literal>RENAME TO</>, and <literal>SET SCHEMA</>
 253    clauses cause ordinary catalog updates that can be rolled back.
 254   </para>
 255
 256   <para>
 257    <command>ALTER SEQUENCE</command> will not immediately affect
 258    <function>nextval</> results in backends,
 259    other than the current one, that have preallocated (cached) sequence
 260    values. They will use up all cached values prior to noticing the changed
 261    sequence generation parameters.  The current backend will be affected
 262    immediately.
 263   </para>
 264
 265   <para>
 266    <command>ALTER SEQUENCE</command> does not affect the <function>currval</>
 267    status for the sequence.  (Before <productname>PostgreSQL</productname>
 268    8.3, it sometimes did.)
 269   </para>
 270
 271   <para>
 272    For historical reasons, <command>ALTER TABLE</command> can be used with
 273    sequences too; but the only variants of <command>ALTER TABLE</command>
 274    that are allowed with sequences are equivalent to the forms shown above.
 275   </para>
 276  </refsect1>
 277
 278  <refsect1>
 279   <title>Examples</title>
 280
 281   <para>
 282    Restart a sequence called <literal>serial</literal>, at 105:
 283 <programlisting>
 284 ALTER SEQUENCE serial RESTART WITH 105;
 285 </programlisting>
 286   </para>
 287  </refsect1>
 288
 289  <refsect1>
 290   <title>Compatibility</title>
 291
 292   <para>
 293    <command>ALTER SEQUENCE</command> conforms to the <acronym>SQL</acronym>
 294    standard, except for the <literal>START WITH</>,
 295    <literal>OWNED BY</>, <literal>OWNER TO</>, <literal>RENAME TO</>, and
 296    <literal>SET SCHEMA</literal> clauses, which are
 297    <productname>PostgreSQL</productname> extensions.
 298   </para>
 299  </refsect1>
 300
 301  <refsect1>
 302   <title>See Also</title>
 303
 304   <simplelist type="inline">
 305    <member><xref linkend="sql-createsequence" endterm="sql-createsequence-title"></member>
 306    <member><xref linkend="sql-dropsequence" endterm="sql-dropsequence-title"></member>
 307   </simplelist>
 308  </refsect1>
 309
 310 </refentry>