create_sequence man page on Scientific

Man page or keyword search:  
man Server   26626 pages
apropos Keyword Search (all sections)
Output format
Scientific logo
[printable version]


       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 ]
	   [ OWNED BY { table.column | NONE } ]

       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 cannot 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 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 might 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

       OWNED BY table.column

	      The OWNED BY 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.  OWNED BY NONE, the default, speci‐
	      fies that there is no such association.

       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 might be no com‐
       piler support for eight-byte integers, in which case sequences use reg‐
       ular integer arithmetic (range -2147483648 to +2147483647).

       Unexpected  results  might  be obtained if a cache 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 last_value accordingly.	Then, the next cache-1
       uses of nextval within that session simply return the preallocated val‐
       ues without touching the sequence object. So, any numbers allocated but
       not used within a session will be lost when that session ends,  result‐
       ing in ``holes'' in the sequence.

       Furthermore, although multiple sessions are guaranteed to allocate dis‐
       tinct sequence values, the values might 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');


       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‐

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

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

       · The OWNED BY clause is a PostgreSQL extension.

       ALTER SEQUENCE [alter_sequence(7)], DROP SEQUENCE [drop_sequence(7)]

SQL - Language Statements	  2013-10-08		    CREATE SEQUENCE(7)

List of man pages available for Scientific

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
Vote for polarhome
Free Shell Accounts :: the biggest list on the net