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