CREATE SEQUENCE - Creates a new sequence number generator


SYNOPSIS

       CREATE SEQUENCE seqname [ INCREMENT increment ]
           [ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
           [ START start ] [ CACHE cache ] [ CYCLE ]

   INPUTS
       seqname
              The name of a sequence to be created.

       increment
              The INCREMENT increment clause is optional. A posi­
              tive value will make an ascending sequence, a nega­
              tive  one a descending sequence.  The default value
              is one (1).

       minvalue
              The optional clause  MINVALUE  minvalue  determines
              the  minimum  value  a  sequence  can generate. The
              defaults are 1 and -2147483647  for  ascending  and
              descending sequences, respectively.

       maxvalue
              The  optional  clause  MAXVALUE maxvalue determines
              the maximum value for the  sequence.  The  defaults
              are  2147483647 and -1 for ascending and descending
              sequences, respectively.

       start  The  optional  START  start  clause   enables   the
              sequence  to  begin anywhere.  The default starting
              value is minvalue for ascending sequences and  max­
              value for descending ones.

       cache  The  CACHE cache 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.

       CYCLE  The  optional  CYCLE  keyword may be used to enable
              the sequence to wrap around when  the  maxvalue  or
              minvalue  has  been  reached  by  an  ascending  or
              descending sequence respectively. If the  limit  is
              reached, the next number generated will be the min­
              value or maxvalue, respectively.

   OUTPUTS
       CREATE Message returned if the command is successful.

              If the sequence specified already exists.

       ERROR: DefineSequence: MINVALUE  (start)  can't  be  >=
              MAXVALUE (max)
              If the specified starting value is out of range.

       ERROR: DefineSequence: START value (start) can't  be  <
              MINVALUE (min)
              If the specified starting value is out of range.

       ERROR: DefineSequence: MINVALUE (min) can't be >= MAXVALUE
              (max)
              If the minimum and maximum values are inconsistent.


DESCRIPTION

       CREATE SEQUENCE will enter a new sequence number generator
       into  the  current  data  base. This involves creating and
       initializing a new single-row table with the name seqname.
       The  generator  will be owned by the user issuing the com­
       mand.

       After a sequence is created,  you  may  use  the  function
       nextval('seqname')  to get a new number from the sequence.
       The function currval('seqname') may be used  to  determine
       the number returned by the last call to nextval('seqname')
       for the specified sequence in the  current  session.   The
       function  setval('seqname',  newvalue)  may be used to set
       the current value of the  specified  sequence.   The  next
       call  to  nextval('seqname')  will  return the given value
       plus the sequence increment.

       Use a query like

       SELECT * FROM seqname;

       to examine the parameters of a sequence.  As  an  alterna­
       tive  to fetching the parameters from the original defini­
       tion as above, you can use

       SELECT last_value FROM seqname;

       to obtain the last value allocated by any backend.

       To avoid blocking of concurrent transactions  that  obtain
       numbers  from  the  same  sequence, a nextval operation is
       never rolled back; that is, once a value has been  fetched
       it  is  considered  used, even if the transaction that did
       the nextval later aborts. This means that aborted transac­
       tions may leave unused "holes" in the sequence of assigned
       values. setval operations are never rolled back, either.

              cache  setting  greater  than  one  is  used  for a
              sequence object that will be used  concurrently  by
              multiple  backends.  Each backend will allocate and
              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 allo­
              cate 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 back­
              end,  whether  or  not  it has yet been returned by
              nextval.  Another consideration is  that  a  setval
              executed  on such a sequence will not be noticed by
              other backends until they have used up any preallo­
              cated values they have cached.

   NOTES
       Use DROP SEQUENCE to remove a sequence.

       Each backend uses its own cache to store preallocated num­
       bers.  Numbers that are cached but not used in the current
       session   will  be  lost,  resulting  in  "holes"  in  the
       sequence.


USAGE

       Create an ascending sequence called  serial,  starting  at
       101:

       CREATE SEQUENCE serial START 101;

       Select the next number from this sequence:

       SELECT NEXTVAL ('serial');

       nextval
       -------


       Use this sequence in an INSERT:

       INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing');

       Set the sequence value after a COPY FROM:

       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;


COMPATIBILITY

   SQL92
       CREATE  SEQUENCE  is a Postgres language extension.  There
       is no CREATE SEQUENCE statement in SQL92.


Man(1) output converted with man2html