File: sql-createsequence.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (705 lines) | stat: -rw-r--r-- 10,831 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>   CREATE SEQUENCE
  </TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="   CREATE RULE
  "
HREF="sql-createrule.html"><LINK
REL="NEXT"
TITLE="   CREATE TABLE
  "
HREF="sql-createtable.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-createrule.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-createtable.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>CREATE SEQUENCE</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>   CREATE SEQUENCE
   &#8212;    Creates a new sequence number generator
  </DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>CREATE SEQUENCE <TT
CLASS="REPLACEABLE"
><I
>seqname</I
></TT
>
    [ INCREMENT <TT
CLASS="REPLACEABLE"
><I
>increment</I
></TT
> ]
    [ MINVALUE  <TT
CLASS="REPLACEABLE"
><I
>minvalue</I
></TT
> ]
    [ MAXVALUE  <TT
CLASS="REPLACEABLE"
><I
>maxvalue</I
></TT
> ]
    [ START     <TT
CLASS="REPLACEABLE"
><I
>start</I
></TT
> ]
    [ CACHE     <TT
CLASS="REPLACEABLE"
><I
>cache</I
></TT
> ]
    [ CYCLE ]
  </PRE
><DIV
CLASS="REFSECT2"
><H3
>    Inputs
   </H3
><P
>   </P
><P
></P
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>seqname</I
></TT
></DT
><DD
><P
>          The name of a sequence to be created.
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>increment</I
></TT
></DT
><DD
><P
>	   The <SPAN
CLASS="OPTION"
>INCREMENT <TT
CLASS="REPLACEABLE"
><I
>increment</I
></TT
></SPAN
> clause is optional. A positive value will make an
	    ascending sequence, a negative one a descending sequence.
 The default value is one (1).
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>minvalue</I
></TT
></DT
><DD
><P
>	   The optional clause <SPAN
CLASS="OPTION"
>MINVALUE
	   <TT
CLASS="REPLACEABLE"
><I
>minvalue</I
></TT
></SPAN
>
	   determines the minimum value
	   a sequence can generate. The defaults are 1 and -2147483647 for
	   ascending and descending sequences, respectively.
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>maxvalue</I
></TT
></DT
><DD
><P
>	   Use the optional clause <SPAN
CLASS="OPTION"
>MAXVALUE
	   <TT
CLASS="REPLACEABLE"
><I
>maxvalue</I
></TT
></SPAN
> to
	   determine the maximum
	   value for the sequence. The defaults are 2147483647 and -1 for
	   ascending and descending sequences, respectively.
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>start</I
></TT
></DT
><DD
><P
>	   The optional <SPAN
CLASS="OPTION"
>START
	    <TT
CLASS="REPLACEABLE"
><I
>start</I
></TT
>
	    clause</SPAN
> enables the sequence to begin anywhere.
	   The default starting value is
	   <TT
CLASS="REPLACEABLE"
><I
>minvalue</I
></TT
>
	   for ascending sequences and
	   <TT
CLASS="REPLACEABLE"
><I
>maxvalue</I
></TT
>
	   for descending ones.
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>cache</I
></TT
></DT
><DD
><P
>	   The <SPAN
CLASS="OPTION"
>CACHE <TT
CLASS="REPLACEABLE"
><I
>cache</I
></TT
></SPAN
> option
	   enables sequence numbers 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.
	  </P
></DD
><DT
>CYCLE</DT
><DD
><P
>	   The optional CYCLE keyword may be used to enable the sequence
	   to continue when the
	   <TT
CLASS="REPLACEABLE"
><I
>maxvalue</I
></TT
> or
	   <TT
CLASS="REPLACEABLE"
><I
>minvalue</I
></TT
> has been
	   reached by
	   an ascending or descending sequence respectively. If the limit is
	   reached, the next number generated will be whatever the
	   <TT
CLASS="REPLACEABLE"
><I
>minvalue</I
></TT
> or
	   <TT
CLASS="REPLACEABLE"
><I
>maxvalue</I
></TT
> is,
	   as appropriate.
	  </P
></DD
></DL
></DIV
><DIV
CLASS="REFSECT2"
><H3
>    Outputs
   </H3
><P
>&#13;       <P
></P
></P><DL
><DT
><SPAN
CLASS="RETURNVALUE"
>CREATE</SPAN
></DT
><DD
><P
>	   Message returned if the command is successful.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>ERROR:  amcreate: '<TT
CLASS="REPLACEABLE"
><I
>seqname</I
></TT
>' relation already exists</SPAN
></DT
><DD
><P
>	   If the sequence specified already exists.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>ERROR:  DefineSequence: START value (<TT
CLASS="REPLACEABLE"
><I
>start</I
></TT
>) can't be &#62; MAXVALUE (<TT
CLASS="REPLACEABLE"
><I
>maxvalue</I
></TT
>)</SPAN
></DT
><DD
><P
>	   If the specified starting value is out of range.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>ERROR:  DefineSequence: START value (<TT
CLASS="REPLACEABLE"
><I
>start</I
></TT
>) can't be &#60; MINVALUE (<TT
CLASS="REPLACEABLE"
><I
>minvalue</I
></TT
>)</SPAN
></DT
><DD
><P
>	   If the specified starting value is out of range.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>ERROR:  DefineSequence: MINVALUE (<TT
CLASS="REPLACEABLE"
><I
>minvalue</I
></TT
>) can't be &#62;= MAXVALUE (<TT
CLASS="REPLACEABLE"
><I
>maxvalue</I
></TT
>)</SPAN
></DT
><DD
><P
>	   If the minimum and maximum values are inconsistant.

   </P
></DD
></DL
><P>
  </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Description
  </H2
><P
>   CREATE SEQUENCE will enter a new sequence number generator
   into the current data base. This involves creating and initialising a
   new single-row
   table with the name <TT
CLASS="REPLACEABLE"
><I
>seqname</I
></TT
>.
   The generator will be "owned" by the user issuing the command.
  </P
><P
>   After a sequence is created, you may use the function
<TT
CLASS="FUNCTION"
>nextval(<TT
CLASS="REPLACEABLE"
><I
>seqname</I
></TT
>)</TT
>
to get a new number from the sequence.
The function
<TT
CLASS="FUNCTION"
>currval('<TT
CLASS="REPLACEABLE"
><I
>seqname</I
></TT
>')</TT
>
 may be used to determine the number returned by the last call to
<TT
CLASS="FUNCTION"
>nextval(<TT
CLASS="REPLACEABLE"
><I
>seqname</I
></TT
>)</TT
>
 for the specified sequence in the current session.
The function
<TT
CLASS="FUNCTION"
>setval('<TT
CLASS="REPLACEABLE"
><I
>seqname</I
></TT
>',
 <TT
CLASS="REPLACEABLE"
><I
>newvalue</I
></TT
>)</TT
>
may be used to set the current value of the specified sequence.
The next call to 
<TT
CLASS="FUNCTION"
>nextval(<TT
CLASS="REPLACEABLE"
><I
>seqname</I
></TT
>)</TT
>
will return the given value plus the sequence increment.
  </P
><P
>   Use a query like
   <PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM sequence_name;
   </PRE
>
   to get the parameters of a sequence.
   Aside from fetching the original
   parameters, you can use
   <PRE
CLASS="PROGRAMLISTING"
>SELECT last_value FROM sequence_name;
   </PRE
>
   to obtain the last value allocated by any backend.
   parameters, you can use
  </P
><P
>   Low-level locking is used to enable multiple simultaneous
   calls to a generator.
  </P
><P
></P
><TABLE
CLASS="CAUTION"
BORDER="1"
WIDTH="100%"
><TR
><TD
ALIGN="CENTER"
><B
>Caution</B
></TD
></TR
><TR
><TD
ALIGN="LEFT"
><P
>  Unexpected results may be obtained if a cache setting greater than one
  is used for a sequence object that will be used concurrently by multiple
  backends.  Each backend will allocate "cache" successive sequence values
  during one access to the sequence object and increase the sequence
  object's last_value accordingly.  Then, the next cache-1 uses of nextval
  within that backend simply return the preallocated values without touching
  the shared object.  So, numbers allocated but not used in the current session
  will be lost.  Furthermore, although multiple backends are guaranteed to
  allocate distinct sequence values, the values may be generated out of
  sequence when all the backends are considered.  (For example, with a cache
  setting of 10, backend A might reserve values 1..10 and return nextval=1, 
then
  backend B might reserve values 11..20 and return nextval=11 before backend
  A has generated nextval=2.)  Thus, with a cache setting of one it is safe
  to assume that nextval values are generated sequentially; with a cache
  setting greater than one you should only assume that the nextval values
  are all distinct, not that they are generated purely sequentially.
  Also, last_value will reflect the latest value reserved by any backend,
  whether or not it has yet been returned by nextval.</P
></TD
></TR
></TABLE
><DIV
CLASS="REFSECT2"
><H3
>    Notes
   </H3
><P
>    Refer to the DROP SEQUENCE statement to remove a sequence.
   </P
><P
>    Each backend uses its own cache to store allocated numbers.
    Numbers that are cached but not used in the current session will be
    lost, resulting in "holes" in the sequence.
   </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Usage
  </H2
><P
>   Create an ascending sequence called <TT
CLASS="LITERAL"
>serial</TT
>, starting at 101:
  </P
><PRE
CLASS="PROGRAMLISTING"
>CREATE SEQUENCE serial START 101;
  </PRE
><P
>   Select the next number from this sequence
   <PRE
CLASS="PROGRAMLISTING"
>SELECT NEXTVAL ('serial');
    
nextval
-------
    114
   </PRE
>
  </P
><P
>   Use this sequence in an INSERT:
   <PRE
CLASS="PROGRAMLISTING"
>INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing');
   </PRE
>
  </P
><P
>   Set the sequence value after a COPY FROM:
   <PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION distributors_id_max() RETURNS INT4
       AS 'SELECT max(id) FROM distributors' 
       LANGUAGE 'sql';
BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', distributors_id_max());
END;
   </PRE
>
  </P
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Compatibility
  </H2
><P
>   <B
CLASS="COMMAND"
>CREATE SEQUENCE</B
> is a <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
 language extension.
  </P
><DIV
CLASS="REFSECT2"
><H3
>    SQL92
   </H3
><P
>    There is no <B
CLASS="COMMAND"
>CREATE SEQUENCE</B
> statement
 in <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
>.
   </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-createrule.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-createtable.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>CREATE RULE</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>CREATE TABLE</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>