create_sequence manpage

Search topic Section


       CREATE SEQUENCE - define a new sequence generator

       CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
	   [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
	   [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

       CREATE  SEQUENCE creates a new sequence number generator. This involves
       creating and initializing a new special single-row table with the  name
       name. The generator will be owned by the user issuing the command.

       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

       After a sequence is created, you use the	 functions  nextval,  currval,
       and  setval  to operate on the sequence. These functions are documented
       in the documentation.

       Although you cannot update a sequence directly, you  can	 use  a	 query

       SELECT * FROM name;

       to  examine the parameters and current state of a sequence. In particu-
       lar, the last_value field of the sequence shows the  last  value	 allo-
       cated  by  any  session.	 (Of course, this value may be obsolete by the
       time it's printed, if other sessions are actively doing nextval calls.)

	      If specified, the sequence object is created only for this  ses-
	      sion,  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.

       name   The name (optionally schema-qualified) of	 the  sequence	to  be

	      The optional clause INCREMENT BY increment 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.


	      The optional clause MINVALUE  minvalue  determines  the  minimum
	      value a sequence can generate. If this clause is not supplied or
	      NO MINVALUE is  specified,  then	defaults  will	be  used.  The
	      defaults	 are   1  and  -263-1  for  ascending  and  descending
	      sequences, respectively.


	      The optional clause MAXVALUE  maxvalue  determines  the  maximum
	      value  for  the  sequence.  If this clause is not supplied or NO
	      MAXVALUE is specified, then default values  will	be  used.  The
	      defaults	 are   263-1  and  -1  for  ascending  and  descending
	      sequences, respectively.

       start  The optional clause START WITH start   allows  the  sequence  to
	      begin  anywhere.	The  default  starting	value  is minvalue for
	      ascending sequences and maxvalue for descending ones.

       cache  The optional clause CACHE cache specifies how many sequence num-
	      bers  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.


       NO CYCLE
	      The  CYCLE  option  allows  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 minvalue or maxvalue,  respec-

	      If  NO  CYCLE  is	 specified,  any  calls	 to  nextval after the
	      sequence has reached its maximum value will return an error.  If
	      neither  CYCLE  or  NO  CYCLE  are  specified,  NO  CYCLE is the

       Use DROP SEQUENCE to remove a sequence.

       Sequences are based on 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
       integer arithmetic (range -2147483648 to +2147483647).

       Unexpected results may be obtained if a cache setting greater than  one
       is  used for a sequence object that will be used concurrently by multi-
       ple sessions. Each session 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 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
       ``holes'' in the sequence.

       Furthermore, although multiple sessions are guaranteed to allocate dis-
       tinct sequence values, the values may be generated out of sequence when
       all the sessions are considered. For example, with a cache  setting  of
       10,  session  A	might  reserve values 1..10 and return nextval=1, then
       session B might reserve values 11..20 and return nextval=11 before ses-
       sion 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  sequen-
       tially.	Also, last_value will reflect the latest value reserved by any
       session, 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 sessions until they have used up any preallo-
       cated values they have cached.

       Create an ascending sequence called serial, starting at 101:

       CREATE SEQUENCE serial START 101;

       Select the next number from this sequence:

       SELECT nextval('serial');


       Use this sequence in an INSERT command:

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

       Update the sequence value after a COPY FROM:

       COPY distributors FROM 'input_file';
       SELECT setval('serial', max(id)) FROM distributors;

       CREATE SEQUENCE conforms to the SQL standard, with the following excep-

       o The standard's AS <data type> expression is not supported.

       o Obtaining the next value is done using the nextval() function instead
	 of the standard's NEXT VALUE FOR expression.

SQL - Language Statements	  2010-12-14		     CREATE SEQUENCE()