doc/src/sgml/ref/create_sequence.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATESEQUENCE">
   7  <refmeta>
   8   <refentrytitle id="sql-createsequence-title">CREATE SEQUENCE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>CREATE SEQUENCE</refname>
  15   <refpurpose>define a new sequence generator</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-createsequence">
  19   <primary>CREATE SEQUENCE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
  25     [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
  26     [ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
  27     [ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
  28 </synopsis>
  29  </refsynopsisdiv>
  30
  31  <refsect1>
  32   <title>Description</title>
  33
  34   <para>
  35    <command>CREATE SEQUENCE</command> creates a new sequence number
  36    generator.  This involves creating and initializing a new special
  37    single-row table with the name <replaceable
  38    class="parameter">name</replaceable>.  The generator will be
  39    owned by the user issuing the command.
  40   </para>
  41
  42   <para>
  43    If a schema name is given then the sequence is created in the
  44    specified schema.  Otherwise it is created in the current schema.
  45    Temporary sequences exist in a special schema, so a schema name cannot be
  46    given when creating a temporary sequence.
  47    The sequence name must be distinct from the name of any other sequence,
  48    table, index, or view in the same schema.
  49   </para>
  50
  51   <para>
  52    After a sequence is created, you use the functions
  53    <function>nextval</function>,
  54    <function>currval</function>, and
  55    <function>setval</function>
  56    to operate on the sequence.  These functions are documented in
  57    <xref linkend="functions-sequence">.
  58   </para>
  59
  60   <para>
  61    Although you cannot update a sequence directly, you can use a query like:
  62
  63 <programlisting>
  64 SELECT * FROM <replaceable>name</replaceable>;
  65 </programlisting>
  66
  67    to examine the parameters and current state of a sequence.  In particular,
  68    the <literal>last_value</> field of the sequence shows the last value
  69    allocated by any session.  (Of course, this value might be obsolete
  70    by the time it's printed, if other sessions are actively doing
  71    <function>nextval</> calls.)
  72   </para>
  73  </refsect1>
  74
  75  <refsect1>
  76   <title>Parameters</title>
  77
  78   <variablelist>
  79    <varlistentry>
  80     <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
  81     <listitem>
  82      <para>
  83       If specified, the sequence object is created only for this
  84       session, and is automatically dropped on session exit.  Existing
  85       permanent sequences with the same name are not visible (in this
  86       session) while the temporary sequence exists, unless they are
  87       referenced with schema-qualified names.
  88      </para>
  89     </listitem>
  90    </varlistentry>
  91
  92    <varlistentry>
  93     <term><replaceable class="parameter">name</replaceable></term>
  94     <listitem>
  95      <para>
  96       The name (optionally schema-qualified) of the sequence to be created.
  97      </para>
  98     </listitem>
  99    </varlistentry>
 100
 101    <varlistentry>
 102     <term><replaceable class="parameter">increment</replaceable></term>
 103     <listitem>
 104      <para>
 105       The optional clause <literal>INCREMENT BY <replaceable
 106       class="parameter">increment</replaceable></literal> specifies
 107       which value is added to the current sequence value to create a
 108       new value.  A positive value will make an ascending sequence, a
 109       negative one a descending sequence.  The default value is 1.
 110      </para>
 111     </listitem>
 112    </varlistentry>
 113
 114    <varlistentry>
 115     <term><replaceable class="parameter">minvalue</replaceable></term>
 116     <term><literal>NO MINVALUE</literal></term>
 117     <listitem>
 118      <para>
 119       The optional clause <literal>MINVALUE <replaceable
 120       class="parameter">minvalue</replaceable></literal> determines
 121       the minimum value a sequence can generate. If this clause is not
 122       supplied or <option>NO MINVALUE</option> is specified, then
 123       defaults will be used.  The defaults are 1 and
 124       -2<superscript>63</>-1 for ascending and descending sequences,
 125       respectively.
 126      </para>
 127     </listitem>
 128    </varlistentry>
 129
 130    <varlistentry>
 131     <term><replaceable class="parameter">maxvalue</replaceable></term>
 132     <term><literal>NO MAXVALUE</literal></term>
 133     <listitem>
 134      <para>
 135       The optional clause <literal>MAXVALUE <replaceable
 136       class="parameter">maxvalue</replaceable></literal> determines
 137       the maximum value for the sequence. If this clause is not
 138       supplied or <option>NO MAXVALUE</option> is specified, then
 139       default values will be used.  The defaults are
 140       2<superscript>63</>-1 and -1 for ascending and descending
 141       sequences, respectively.
 142      </para>
 143     </listitem>
 144    </varlistentry>
 145
 146    <varlistentry>
 147     <term><replaceable class="parameter">start</replaceable></term>
 148     <listitem>
 149      <para>
 150       The optional clause <literal>START WITH <replaceable
 151       class="parameter">start</replaceable> </literal> allows the
 152       sequence to begin anywhere.  The default starting value is
 153       <replaceable class="parameter">minvalue</replaceable> for
 154       ascending sequences and <replaceable
 155       class="parameter">maxvalue</replaceable> for descending ones.
 156      </para>
 157     </listitem>
 158    </varlistentry>
 159
 160    <varlistentry>
 161     <term><replaceable class="parameter">cache</replaceable></term>
 162     <listitem>
 163      <para>
 164       The optional clause <literal>CACHE <replaceable
 165       class="parameter">cache</replaceable></literal> specifies how
 166       many sequence numbers are to be preallocated and stored in
 167       memory for faster access. The minimum value is 1 (only one value
 168       can be generated at a time, i.e., no cache), and this is also the
 169       default.
 170      </para>
 171     </listitem>
 172    </varlistentry>
 173
 174    <varlistentry>
 175     <term><literal>CYCLE</literal></term>
 176     <term><literal>NO CYCLE</literal></term>
 177     <listitem>
 178      <para>
 179       The <literal>CYCLE</literal> option allows the sequence to wrap
 180       around when the <replaceable
 181       class="parameter">maxvalue</replaceable> or <replaceable
 182       class="parameter">minvalue</replaceable> has been reached by an
 183       ascending or descending sequence respectively. If the limit is
 184       reached, the next number generated will be the <replaceable
 185       class="parameter">minvalue</replaceable> or <replaceable
 186       class="parameter">maxvalue</replaceable>, respectively.
 187      </para>
 188
 189      <para>
 190       If <literal>NO CYCLE</literal> is specified, any calls to
 191       <function>nextval</function> after the sequence has reached its
 192       maximum value will return an error.  If neither
 193       <literal>CYCLE</literal> or <literal>NO CYCLE</literal> are
 194       specified, <literal>NO CYCLE</literal> is the default.
 195      </para>
 196     </listitem>
 197    </varlistentry>
 198
 199    <varlistentry>
 200     <term><literal>OWNED BY</literal> <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable></term>
 201     <term><literal>OWNED BY NONE</literal></term>
 202     <listitem>
 203      <para>
 204       The <literal>OWNED BY</literal> option causes the sequence to be
 205       associated with a specific table column, such that if that column
 206       (or its whole table) is dropped, the sequence will be automatically
 207       dropped as well.  The specified table must have the same owner and be in
 208       the same schema as the sequence.
 209       <literal>OWNED BY NONE</literal>, the default, specifies that there
 210       is no such association.
 211      </para>
 212     </listitem>
 213    </varlistentry>
 214   </variablelist>
 215  </refsect1>
 216
 217  <refsect1>
 218   <title>Notes</title>
 219
 220   <para>
 221    Use <command>DROP SEQUENCE</command> to remove a sequence.
 222   </para>
 223
 224   <para>
 225    Sequences are based on <type>bigint</> arithmetic, so the range
 226    cannot exceed the range of an eight-byte integer
 227    (-9223372036854775808 to 9223372036854775807).  On some older
 228    platforms, there might be no compiler support for eight-byte
 229    integers, in which case sequences use regular <type>integer</>
 230    arithmetic (range -2147483648 to +2147483647).
 231   </para>
 232
 233   <para>
 234    Unexpected results might be obtained if a <replaceable
 235    class="parameter">cache</replaceable> setting greater than one is
 236    used for a sequence object that will be used concurrently by
 237    multiple sessions.  Each session will allocate and cache successive
 238    sequence values during one access to the sequence object and
 239    increase the sequence object's <literal>last_value</> accordingly.
 240    Then, the next <replaceable class="parameter">cache</replaceable>-1
 241    uses of <function>nextval</> within that session simply return the
 242    preallocated values without touching the sequence object.  So, any
 243    numbers allocated but not used within a session will be lost when
 244    that session ends, resulting in <quote>holes</quote> in the
 245    sequence.
 246   </para>
 247
 248   <para>
 249    Furthermore, although multiple sessions are guaranteed to allocate
 250    distinct sequence values, the values might be generated out of
 251    sequence when all the sessions are considered.  For example, with
 252    a <replaceable class="parameter">cache</replaceable> setting of 10,
 253    session A might reserve values 1..10 and return
 254    <function>nextval</function>=1, then session B might reserve values
 255    11..20 and return <function>nextval</function>=11 before session A
 256    has generated <literal>nextval</literal>=2.  Thus, with a
 257    <replaceable class="parameter">cache</replaceable> setting of one
 258    it is safe to assume that <function>nextval</> values are generated
 259    sequentially; with a <replaceable
 260    class="parameter">cache</replaceable> setting greater than one you
 261    should only assume that the <function>nextval</> values are all
 262    distinct, not that they are generated purely sequentially.  Also,
 263    <literal>last_value</> will reflect the latest value reserved by
 264    any session, whether or not it has yet been returned by
 265    <function>nextval</>.
 266   </para>
 267
 268   <para>
 269    Another consideration is that a <function>setval</> executed on
 270    such a sequence will not be noticed by other sessions until they
 271    have used up any preallocated values they have cached.
 272   </para>
 273  </refsect1>
 274
 275  <refsect1>
 276   <title>Examples</title>
 277
 278   <para>
 279    Create an ascending sequence called <literal>serial</literal>, starting at 101:
 280 <programlisting>
 281 CREATE SEQUENCE serial START 101;
 282 </programlisting>
 283   </para>
 284
 285   <para>
 286    Select the next number from this sequence:
 287 <programlisting>
 288 SELECT nextval('serial');
 289
 290  nextval
 291 ---------
 292      101
 293 </programlisting>
 294   </para>
 295
 296   <para>
 297    Select the next number from this sequence:
 298 <programlisting>
 299 SELECT nextval('serial');
 300
 301  nextval
 302 ---------
 303      102
 304 </programlisting>
 305   </para>
 306
 307   <para>
 308    Use this sequence in an <command>INSERT</command> command:
 309 <programlisting>
 310 INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
 311 </programlisting>
 312   </para>
 313
 314   <para>
 315    Update the sequence value after a <command>COPY FROM</command>:
 316 <programlisting>
 317 BEGIN;
 318 COPY distributors FROM 'input_file';
 319 SELECT setval('serial', max(id)) FROM distributors;
 320 END;
 321 </programlisting>
 322   </para>
 323  </refsect1>
 324
 325  <refsect1>
 326   <title>Compatibility</title>
 327
 328   <para>
 329    <command>CREATE SEQUENCE</command> conforms to the <acronym>SQL</acronym>
 330    standard, with the following exceptions:
 331    <itemizedlist>
 332     <listitem>
 333      <para>
 334       The standard's <literal>AS &lt;data type&gt;</literal> expression is not
 335       supported.
 336      </para>
 337     </listitem>
 338     <listitem>
 339      <para>
 340       Obtaining the next value is done using the <function>nextval()</>
 341       function instead of the standard's <command>NEXT VALUE FOR</command>
 342       expression.
 343      </para>
 344     </listitem>
 345     <listitem>
 346      <para>
 347       The <literal>OWNED BY</> clause is a <productname>PostgreSQL</>
 348       extension.
 349      </para>
 350     </listitem>
 351    </itemizedlist>
 352   </para>
 353  </refsect1>
 354
 355  <refsect1>
 356   <title>See Also</title>
 357
 358   <simplelist type="inline">
 359    <member><xref linkend="sql-altersequence" endterm="sql-altersequence-title"></member>
 360    <member><xref linkend="sql-dropsequence" endterm="sql-dropsequence-title"></member>
 361   </simplelist>
 362  </refsect1>
 363
 364 </refentry>