doc/src/sgml/ref/create_sequence.sgml - hawq - Git at Google

 <!--
 $PostgreSQL: pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.45 2006/09/16 00:30:17 momjian Exp $
 PostgreSQL documentation
 -->

 <refentry id="SQL-CREATESEQUENCE">
  <refmeta>
   <refentrytitle id="sql-createsequence-title">CREATE SEQUENCE</refentrytitle>
   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  </refmeta>

  <refnamediv>
   <refname>CREATE SEQUENCE</refname>
   <refpurpose>define a new sequence generator</refpurpose>
  </refnamediv>

  <indexterm zone="sql-createsequence">
   <primary>CREATE SEQUENCE</primary>
  </indexterm>

  <refsynopsisdiv>
 <synopsis>
 CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
     [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
     [ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
     [ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
 </synopsis>
  </refsynopsisdiv>

  <refsect1>
   <title>Description</title>

   <para>
    <command>CREATE SEQUENCE</command> creates a new sequence number
    generator.  This involves creating and initializing a new special
    single-row table with the name <replaceable
    class="parameter">name</replaceable>.  The generator will be
    owned by the user issuing the command.
   </para>

   <para>
    If a schema name is given then the sequence is created in the
    specified schema.  Otherwise it is created in the current schema.
    Temporary sequences exist in a special schema, so a schema name may not be
    given when creating a temporary sequence.
    The sequence name must be distinct from the name of any other sequence,
    table, index, or view in the same schema.
   </para>

   <para>
    After a sequence is created, you use the functions
    <function>nextval</function>,
    <function>currval</function>, and
    <function>setval</function>
    to operate on the sequence.  These functions are documented in
    <xref linkend="functions-sequence">.
   </para>

   <para>
    Although you cannot update a sequence directly, you can use a query like

 <programlisting>
 SELECT * FROM <replaceable>name</replaceable>;
 </programlisting>

    to examine the parameters and current state of a sequence.  In particular,
    the <literal>last_value</> field of the sequence shows the last value
    allocated by any session.  (Of course, this value may be obsolete
    by the time it's printed, if other sessions are actively doing
    <function>nextval</> calls.)
   </para>
  </refsect1>

  <refsect1>
   <title>Parameters</title>

   <variablelist>
    <varlistentry>
     <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
     <listitem>
      <para>
       If specified, the sequence object is created only for this
       session, and is automatically dropped on session exit.  Existing
       permanent sequences with the same name are not visible (in this
       session) while the temporary sequence exists, unless they are
       referenced with schema-qualified names.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">name</replaceable></term>
     <listitem>
      <para>
       The name (optionally schema-qualified) of the sequence to be created.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">increment</replaceable></term>
     <listitem>
      <para>
       The optional clause <literal>INCREMENT BY <replaceable
       class="parameter">increment</replaceable></literal> specifies
       which value is added to the current sequence value to create a
       new value.  A positive value will make an ascending sequence, a
       negative one a descending sequence.  The default value is 1.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">minvalue</replaceable></term>
     <term><literal>NO MINVALUE</literal></term>
     <listitem>
      <para>
       The optional clause <literal>MINVALUE <replaceable
       class="parameter">minvalue</replaceable></literal> determines
       the minimum value a sequence can generate. If this clause is not
       supplied or <option>NO MINVALUE</option> is specified, then
       defaults will be used.  The defaults are 1 and
       -2<superscript>63</>-1 for ascending and descending sequences,
       respectively.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">maxvalue</replaceable></term>
     <term><literal>NO MAXVALUE</literal></term>
     <listitem>
      <para>
       The optional clause <literal>MAXVALUE <replaceable
       class="parameter">maxvalue</replaceable></literal> determines
       the maximum value for the sequence. If this clause is not
       supplied or <option>NO MAXVALUE</option> is specified, then
       default values will be used.  The defaults are
       2<superscript>63</>-1 and -1 for ascending and descending
       sequences, respectively.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">start</replaceable></term>
     <listitem>
      <para>
       The optional clause <literal>START WITH <replaceable
       class="parameter">start</replaceable> </literal> allows the
       sequence to begin anywhere.  The default starting value is
       <replaceable class="parameter">minvalue</replaceable> for
       ascending sequences and <replaceable
       class="parameter">maxvalue</replaceable> for descending ones.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">cache</replaceable></term>
     <listitem>
      <para>
       The optional clause <literal>CACHE <replaceable
       class="parameter">cache</replaceable></literal> specifies how
       many sequence numbers are to be preallocated and stored in
       memory for faster access. The minimum value is 1 (only one value
       can be generated at a time, i.e., no cache), and this is also the
       default.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>CYCLE</literal></term>
     <term><literal>NO CYCLE</literal></term>
     <listitem>
      <para>
       The <literal>CYCLE</literal> option allows the sequence to wrap
       around when the <replaceable
       class="parameter">maxvalue</replaceable> or <replaceable
       class="parameter">minvalue</replaceable> has been reached by an
       ascending or descending sequence respectively. If the limit is
       reached, the next number generated will be the <replaceable
       class="parameter">minvalue</replaceable> or <replaceable
       class="parameter">maxvalue</replaceable>, respectively.
      </para>

      <para>
       If <literal>NO CYCLE</literal> is specified, any calls to
       <function>nextval</function> after the sequence has reached its
       maximum value will return an error.  If neither
       <literal>CYCLE</literal> or <literal>NO CYCLE</literal> are
       specified, <literal>NO CYCLE</literal> is the default.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>OWNED BY</literal> <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable></term>
     <term><literal>OWNED BY NONE</literal></term>
     <listitem>
      <para>
       The <literal>OWNED BY</literal> option causes the sequence to be
       associated with a specific table column, such that if that column
       (or its whole table) is dropped, the sequence will be automatically
       dropped as well.  The specified table must have the same owner and be in
       the same schema as the sequence.
       <literal>OWNED BY NONE</literal>, the default, specifies that there
       is no such association.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </refsect1>

  <refsect1>
   <title>Notes</title>

   <para>
    Use <command>DROP SEQUENCE</command> to remove a sequence.
   </para>

   <para>
    Sequences are based on <type>bigint</> arithmetic, so the range
    cannot exceed the range of an eight-byte integer
    (-9223372036854775808 to 9223372036854775807).  On some older
    platforms, there may be no compiler support for eight-byte
    integers, in which case sequences use regular <type>integer</>
    arithmetic (range -2147483648 to +2147483647).
   </para>

   <para>
    Unexpected results may be obtained if a <replaceable
    class="parameter">cache</replaceable> setting greater than one is
    used for a sequence object that will be used concurrently by
    multiple sessions.  Each session will allocate and cache successive
    sequence values during one access to the sequence object and
    increase the sequence object's <literal>last_value</> accordingly.
    Then, the next <replaceable class="parameter">cache</replaceable>-1
    uses of <function>nextval</> within that session simply return the
    preallocated values without touching the sequence object.  So, any
    numbers allocated but not used within a session will be lost when
    that session ends, resulting in <quote>holes</quote> in the
    sequence.
   </para>

   <para>
    Furthermore, although multiple sessions are guaranteed to allocate
    distinct sequence values, the values may be generated out of
    sequence when all the sessions are considered.  For example, with
    a <replaceable class="parameter">cache</replaceable> setting of 10,
    session A might reserve values 1..10 and return
    <function>nextval</function>=1, then session B might reserve values
    11..20 and return <function>nextval</function>=11 before session A
    has generated <literal>nextval</literal>=2.  Thus, with a
    <replaceable class="parameter">cache</replaceable> setting of one
    it is safe to assume that <function>nextval</> values are generated
    sequentially; with a <replaceable
    class="parameter">cache</replaceable> setting greater than one you
    should only assume that the <function>nextval</> values are all
    distinct, not that they are generated purely sequentially.  Also,
    <literal>last_value</> will reflect the latest value reserved by
    any session, whether or not it has yet been returned by
    <function>nextval</>.
   </para>

   <para>
    Another consideration is that a <function>setval</> executed on
    such a sequence will not be noticed by other sessions until they
    have used up any preallocated values they have cached.
   </para>
  </refsect1>

  <refsect1>
   <title>Examples</title>

   <para>
    Create an ascending sequence called <literal>serial</literal>, starting at 101:
 <programlisting>
 CREATE SEQUENCE serial START 101;
 </programlisting>
   </para>

   <para>
    Select the next number from this sequence:
 <programlisting>
 SELECT nextval('serial');

  nextval
 ---------
      114
 </programlisting>
   </para>

   <para>
    Use this sequence in an <command>INSERT</command> command:
 <programlisting>
 INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
 </programlisting>
   </para>

   <para>
    Update the sequence value after a <command>COPY FROM</command>:
 <programlisting>
 BEGIN;
 COPY distributors FROM 'input_file';
 SELECT setval('serial', max(id)) FROM distributors;
 END;
 </programlisting>
   </para>
  </refsect1>

  <refsect1>
   <title>Compatibility</title>

   <para>
    <command>CREATE SEQUENCE</command> conforms to the <acronym>SQL</acronym>
    standard, with the following exceptions:
    <itemizedlist>
     <listitem>
      <para>
       The standard's <literal>AS &lt;data type&gt;</literal> expression is not
       supported.
      </para>
     </listitem>
     <listitem>
      <para>
       Obtaining the next value is done using the <function>nextval()</>
       function instead of the standard's <command>NEXT VALUE FOR</command>
       expression.
      </para>
     </listitem>
     <listitem>
      <para>
       The <literal>OWNED BY</> clause is a <productname>PostgreSQL</>
       extension.
      </para>
     </listitem>
    </itemizedlist>
   </para>
  </refsect1>

  <refsect1>
   <title>See Also</title>

   <simplelist type="inline">
    <member><xref linkend="sql-altersequence" endterm="sql-altersequence-title"></member>
    <member><xref linkend="sql-dropsequence" endterm="sql-dropsequence-title"></member>
   </simplelist>
  </refsect1>

 </refentry>
	<!--
	$PostgreSQL: pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.45 2006/09/16 00:30:17 momjian Exp $
	PostgreSQL documentation
	-->

	<refentry id="SQL-CREATESEQUENCE">
	<refmeta>
	<refentrytitle id="sql-createsequence-title">CREATE SEQUENCE</refentrytitle>
	<refmiscinfo>SQL - Language Statements</refmiscinfo>
	</refmeta>

	<refnamediv>
	<refname>CREATE SEQUENCE</refname>
	<refpurpose>define a new sequence generator</refpurpose>
	</refnamediv>

	<indexterm zone="sql-createsequence">
	<primary>CREATE SEQUENCE</primary>
	</indexterm>

	<refsynopsisdiv>
	<synopsis>
	CREATE [ TEMPORARY \| TEMP ] SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
	[ MINVALUE <replaceable class="parameter">minvalue</replaceable> \| NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> \| NO MAXVALUE ]
	[ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
	[ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> \| NONE } ]
	</synopsis>
	</refsynopsisdiv>

	<refsect1>
	<title>Description</title>

	<para>
	<command>CREATE SEQUENCE</command> creates a new sequence number
	generator. This involves creating and initializing a new special
	single-row table with the name <replaceable
	class="parameter">name</replaceable>. The generator will be
	owned by the user issuing the command.
	</para>

	<para>
	If a schema name is given then the sequence is created in the
	specified schema. Otherwise it is created in the current schema.
	Temporary sequences exist in a special schema, so a schema name may not be
	given when creating a temporary sequence.
	The sequence name must be distinct from the name of any other sequence,
	table, index, or view in the same schema.
	</para>

	<para>
	After a sequence is created, you use the functions
	<function>nextval</function>,
	<function>currval</function>, and
	<function>setval</function>
	to operate on the sequence. These functions are documented in
	<xref linkend="functions-sequence">.
	</para>

	<para>
	Although you cannot update a sequence directly, you can use a query like

	<programlisting>
	SELECT * FROM <replaceable>name</replaceable>;
	</programlisting>

	to examine the parameters and current state of a sequence. In particular,
	the <literal>last_value</> field of the sequence shows the last value
	allocated by any session. (Of course, this value may be obsolete
	by the time it's printed, if other sessions are actively doing
	<function>nextval</> calls.)
	</para>
	</refsect1>

	<refsect1>
	<title>Parameters</title>

	<variablelist>
	<varlistentry>
	<term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
	<listitem>
	<para>
	If specified, the sequence object is created only for this
	session, and is automatically dropped on session exit. Existing
	permanent sequences with the same name are not visible (in this
	session) while the temporary sequence exists, unless they are
	referenced with schema-qualified names.
	</para>
	</listitem>
	</varlistentry>

	<varlistentry>
	<term><replaceable class="parameter">name</replaceable></term>
	<listitem>
	<para>
	The name (optionally schema-qualified) of the sequence to be created.
	</para>
	</listitem>
	</varlistentry>

	<varlistentry>
	<term><replaceable class="parameter">increment</replaceable></term>
	<listitem>
	<para>
	The optional clause <literal>INCREMENT BY <replaceable
	class="parameter">increment</replaceable></literal> specifies
	which value is added to the current sequence value to create a
	new value. A positive value will make an ascending sequence, a
	negative one a descending sequence. The default value is 1.
	</para>
	</listitem>
	</varlistentry>

	<varlistentry>
	<term><replaceable class="parameter">minvalue</replaceable></term>
	<term><literal>NO MINVALUE</literal></term>
	<listitem>
	<para>
	The optional clause <literal>MINVALUE <replaceable
	class="parameter">minvalue</replaceable></literal> determines
	the minimum value a sequence can generate. If this clause is not
	supplied or <option>NO MINVALUE</option> is specified, then
	defaults will be used. The defaults are 1 and
	-2<superscript>63</>-1 for ascending and descending sequences,
	respectively.
	</para>
	</listitem>
	</varlistentry>

	<varlistentry>
	<term><replaceable class="parameter">maxvalue</replaceable></term>
	<term><literal>NO MAXVALUE</literal></term>
	<listitem>
	<para>
	The optional clause <literal>MAXVALUE <replaceable
	class="parameter">maxvalue</replaceable></literal> determines
	the maximum value for the sequence. If this clause is not
	supplied or <option>NO MAXVALUE</option> is specified, then
	default values will be used. The defaults are
	2<superscript>63</>-1 and -1 for ascending and descending
	sequences, respectively.
	</para>
	</listitem>
	</varlistentry>

	<varlistentry>
	<term><replaceable class="parameter">start</replaceable></term>
	<listitem>
	<para>
	The optional clause <literal>START WITH <replaceable
	class="parameter">start</replaceable> </literal> allows the
	sequence to begin anywhere. The default starting value is
	<replaceable class="parameter">minvalue</replaceable> for
	ascending sequences and <replaceable
	class="parameter">maxvalue</replaceable> for descending ones.
	</para>
	</listitem>
	</varlistentry>

	<varlistentry>
	<term><replaceable class="parameter">cache</replaceable></term>
	<listitem>
	<para>
	The optional clause <literal>CACHE <replaceable
	class="parameter">cache</replaceable></literal> specifies how
	many sequence numbers are to be preallocated and stored in
	memory for faster access. The minimum value is 1 (only one value
	can be generated at a time, i.e., no cache), and this is also the
	default.
	</para>
	</listitem>
	</varlistentry>

	<varlistentry>
	<term><literal>CYCLE</literal></term>
	<term><literal>NO CYCLE</literal></term>
	<listitem>
	<para>
	The <literal>CYCLE</literal> option allows the sequence to wrap
	around when the <replaceable
	class="parameter">maxvalue</replaceable> or <replaceable
	class="parameter">minvalue</replaceable> has been reached by an
	ascending or descending sequence respectively. If the limit is
	reached, the next number generated will be the <replaceable
	class="parameter">minvalue</replaceable> or <replaceable
	class="parameter">maxvalue</replaceable>, respectively.
	</para>

	<para>
	If <literal>NO CYCLE</literal> is specified, any calls to
	<function>nextval</function> after the sequence has reached its
	maximum value will return an error. If neither
	<literal>CYCLE</literal> or <literal>NO CYCLE</literal> are
	specified, <literal>NO CYCLE</literal> is the default.
	</para>
	</listitem>
	</varlistentry>

	<varlistentry>
	<term><literal>OWNED BY</literal> <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable></term>
	<term><literal>OWNED BY NONE</literal></term>
	<listitem>
	<para>
	The <literal>OWNED BY</literal> option causes the sequence to be
	associated with a specific table column, such that if that column
	(or its whole table) is dropped, the sequence will be automatically
	dropped as well. The specified table must have the same owner and be in
	the same schema as the sequence.
	<literal>OWNED BY NONE</literal>, the default, specifies that there
	is no such association.
	</para>
	</listitem>
	</varlistentry>
	</variablelist>
	</refsect1>

	<refsect1>
	<title>Notes</title>

	<para>
	Use <command>DROP SEQUENCE</command> to remove a sequence.
	</para>

	<para>
	Sequences are based on <type>bigint</> arithmetic, so the range
	cannot exceed the range of an eight-byte integer
	(-9223372036854775808 to 9223372036854775807). On some older
	platforms, there may be no compiler support for eight-byte
	integers, in which case sequences use regular <type>integer</>
	arithmetic (range -2147483648 to +2147483647).
	</para>

	<para>
	Unexpected results may be obtained if a <replaceable
	class="parameter">cache</replaceable> setting greater than one is
	used for a sequence object that will be used concurrently by
	multiple sessions. Each session will allocate and cache successive
	sequence values during one access to the sequence object and
	increase the sequence object's <literal>last_value</> accordingly.
	Then, the next <replaceable class="parameter">cache</replaceable>-1
	uses of <function>nextval</> within that session simply return the
	preallocated values without touching the sequence object. So, any
	numbers allocated but not used within a session will be lost when
	that session ends, resulting in <quote>holes</quote> in the
	sequence.
	</para>

	<para>
	Furthermore, although multiple sessions are guaranteed to allocate
	distinct sequence values, the values may be generated out of
	sequence when all the sessions are considered. For example, with
	a <replaceable class="parameter">cache</replaceable> setting of 10,
	session A might reserve values 1..10 and return
	<function>nextval</function>=1, then session B might reserve values
	11..20 and return <function>nextval</function>=11 before session A
	has generated <literal>nextval</literal>=2. Thus, with a
	<replaceable class="parameter">cache</replaceable> setting of one
	it is safe to assume that <function>nextval</> values are generated
	sequentially; with a <replaceable
	class="parameter">cache</replaceable> setting greater than one you
	should only assume that the <function>nextval</> values are all
	distinct, not that they are generated purely sequentially. Also,
	<literal>last_value</> will reflect the latest value reserved by
	any session, whether or not it has yet been returned by
	<function>nextval</>.
	</para>

	<para>
	Another consideration is that a <function>setval</> executed on
	such a sequence will not be noticed by other sessions until they
	have used up any preallocated values they have cached.
	</para>
	</refsect1>

	<refsect1>
	<title>Examples</title>

	<para>
	Create an ascending sequence called <literal>serial</literal>, starting at 101:
	<programlisting>
	CREATE SEQUENCE serial START 101;
	</programlisting>
	</para>

	<para>
	Select the next number from this sequence:
	<programlisting>
	SELECT nextval('serial');

	nextval
	---------
	114
	</programlisting>
	</para>

	<para>
	Use this sequence in an <command>INSERT</command> command:
	<programlisting>
	INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
	</programlisting>
	</para>

	<para>
	Update the sequence value after a <command>COPY FROM</command>:
	<programlisting>
	BEGIN;
	COPY distributors FROM 'input_file';
	SELECT setval('serial', max(id)) FROM distributors;
	END;
	</programlisting>
	</para>
	</refsect1>

	<refsect1>
	<title>Compatibility</title>

	<para>
	<command>CREATE SEQUENCE</command> conforms to the <acronym>SQL</acronym>
	standard, with the following exceptions:
	<itemizedlist>
	<listitem>
	<para>
	The standard's <literal>AS <data type></literal> expression is not
	supported.
	</para>
	</listitem>
	<listitem>
	<para>
	Obtaining the next value is done using the <function>nextval()</>
	function instead of the standard's <command>NEXT VALUE FOR</command>
	expression.
	</para>
	</listitem>
	<listitem>
	<para>
	The <literal>OWNED BY</> clause is a <productname>PostgreSQL</>
	extension.
	</para>
	</listitem>
	</itemizedlist>
	</para>
	</refsect1>

	<refsect1>
	<title>See Also</title>

	<simplelist type="inline">
	<member><xref linkend="sql-altersequence" endterm="sql-altersequence-title"></member>
	<member><xref linkend="sql-dropsequence" endterm="sql-dropsequence-title"></member>
	</simplelist>
	</refsect1>

	</refentry>